What is the Query, Basic Sub-Query, Complex Sub-Query ?

Query,Basic Sub-Query,Complex Sub-Query

Query

A database query is the use of a select statement to display desired information based on conditions from one or more tables. Different tables in the database store different information. By using query user can view his desired information as desired. For example, let's say that the employees database contains information about many employees. It can display the results of any employee who has joined in the last one year.

Use of Select statement: A command of data query language is select statement. A database query is created using SQL statements. A select statement can be used to display data from multiple columns from one or more tables. But remember that select is not an independent statement. It must be accompanied by one or more other clauses for it to work. Some clauses must be used with this, while some clauses are optional. The most commonly used statement in SQL queries is the select statement. Form clause must be used with this statement.

The four keywords or clauses that are used as part of the select statement are:

form

where

Group by

order by 

Usage of select statement:

Using the select statement with the form clause and or displays data in one or more columns from multiple tables. The columns are indicated by the select part of this statement, and the form part indicates that those columns will be selected from some table. The syntax for using the select statement is--

select [ * ] all | distinct column_n]

 from table_x

Here is the list of columns after the select keyword. Only these columns will be displayed in the query. Then the table name is specified after the from keyword. One or more names can be specified here just like columns. However, table names must be separated by commas (,). Note that column names are also separated by commas. And an asterisk (*) can be used to show all columns. And if the all option is used, each column value, even with multiple values, will be displayed. If distinct option is used then each unique row value will be displayed i.e. if multiple row values ​​of a column are same then only one row will be displayed. By default the all option works, only the distinct option needs to be specified differently. Earlier we saw the use of select many times.

From Clause:

The From clause must be used with a select statement. Any query must require the A clause. The purpose of the from clause is to specify which table to retrieve data from. The From clause can contain one or more table names. However, at least one table name must be specified in the from clause. The syntax for using the from statement is--

from table 1, table_2, table_x

We have seen the use of from many times before.

where clause:

Various conditions can be used to conditionally display certain data in a query. Only data that meets these conditions will be viewed through that query. The where clause is used to specify such conditions. A where clause can contain multiple conditions. If there are multiple conditions, they are joined by AND and OR operators. Apart from these operators, there are some other operators, which express different types of conditions. The syntax for using the select statement is--

select [* | all | distinct column_1, column_2] from table_1, table 2

where [ condition_1 | expression_1] [and condition_2 | expression_2]

Sub-Query

A sub-query is another query inside the where clause of a query, the resulting value of which serves as the value of the where clause. Simply put, a subquery is a query within a query. The result obtained through this sub-query will serve as the value of the where clause, or the value obtained through the sub-query will serve as the value of the condition of the query. Subqueries are used with select, insert, update and delete statements. Relationships between tables can be established by using subqueries as AOKs for one or more conditions. When a subquery is used within a query, the subquery is executed first, then the original query is executed using the result of the subquery. The result of the sub-query acts as part of the Where clause expression. A subquery can be used as part of a where or having clause expression within a query. Logical and relational operators can be used within sub-queries. It should be noted that all the rules of the query will also apply to the sub-query. Subqueries must follow other rules like joins, functions, transformations and queries. Sub-queries must be enclosed in brackets. A select statement can contain only one column in a subquery, unless the main query contains multiple columns as a condition of the subquery.

order by cannot be used in sub-queries, but order by can be used in main queries. However, group by can be used in sub-queries to get results like order by.

Subqueries that return multiple values ​​can only be used with multiple value comparison operators, such as the in operator.

A select statement in a subquery cannot specify a column whose value is BLOB, ARRAR, CLOB, or NCLOB.

A subquery cannot be placed directly with a function.

The between operator cannot be used with subqueries, but the bwteen operator can be used between subqueries.

 

Complex sub-query: A compound query is formed by joining multiple queries through an operator. But both select statements must have equal number of columns. Because the result of both select statements will be added to this column.

Compound operators are used to combine two or more Select statements and combine their results. Duplicate records are not shown in the output as a result of using these operators. Data held in multiple fields can be viewed together using compound operators.

Compound queries can be used to view a result calculation using multiple queries. Writing compound queries is much easier than writing single queries with complex conditions. Compound queries are also easier to understand and therefore less error-prone to write.

Compound operators may vary by database system. However, according to the ANSI standard of SQL, compound queries can be made into multiple queries by using uhion, union all, intersect and except operators.

Union Operator: The union operator is used to join two or more select statements. Adding in this way does not result in any duplicate records. This means that if a row is found in the first query, it will not show up in the second query. When the Union operator is used, each select statement must have the same number of columns, the same number of column expressions, and the same data type in the same order. However, the columns will not be of the same length.