Query and Subquery in SQL
SQL Queries :
A query is an inquiry into the database using the select command. Consider the following relations
Example of SQL Queries :
Query 1 : Get full details of all employees.
Solution : SELECT * FROM Employee;
Query 2 : Get the age of all the employees.
Solution : SELECT Age FROM Employee;
Query 3 : Get the age of all the employees with no duplicates.
Solution : SELECT DISTINCT Age FROM Employee;
Query 4 : Get the employee number and name of the employees whose Salary is greater than 25000.
Solution : SELECT Eno, Name FROM Employee WHERE Salary>25000;
Division of SQL Queries :
Based on the filtering Activity, the SQL queries are divided into 5 groups :
- Queries that uses basic search operations.
- Queries that uses range operations. (Using BETWEEN & NOT BETWEEN operator)
- Queries that uses composition operations. (Using AND & OR operator)
- Queries that uses pattern matching. (Using %, _, and /(escape symbol))
- Query that uses null and not null.
Sub-Queries or Nested Queries :
A subquery is a query within another query i. e. A query embedded in another query is called a subquery. It is also known as nested query. Subquery is used to return data that will be used in the main query as a condition, to further restrict the data to be retrieved.
Subqueries are of 2 types :
- Independent Nested Query : Inner query doesn’t depend on outer query.
- Co-Related Nested Query : Inner query uses attributes of outer query, hence dependent on outer query.
Examples of SubQueries :
Subquery 1: Find names of employees who work in Department number 11.
Solution : SELECT Name FROM Employee WHERE Employee.Eno = (SELECT Work_IN.Eno FROM Work_IN WHERE Dno =11);
Subquery 2: Find name of employee whose basic pay is Greater then all basic pay of the employees working in Department number 10.
Solution : SELECT Name FROM Employee WHERE Salary > ALL(SELECT Salary FROM Employee WHERE Employee.Eno = (SELECT Work_IN.Eno FROM Work_IN WHERE Dno = 10))
Subquery 3 : Find the names of employees whose basic pay is greater than the average basic pay.
Solution : SELECT Name From Employee WHERE Salary > (SELECT AVG(Salary) FROM Employee);
Characteristics of subquery:
- The inquiry will run first and substitute its results in outer query.
- The variable from outer query can be used in the inner query but reverse is not true.
- The subquery can be used in where and having clauses and sometimes in from clause also.
Classification of sub query
- Uncorelated subquery : Inner query will run always only once. If it is independent of outer query then it is called uncorrelated subquery. For Example :
SELECT Name FROM Employee WHERE Employee.Eno = (SELECT Work_IN.Eno FROM Work_IN);
- Corelated Subquery : Inner query will run some time as many times as the number of rows are available in `the outer query. If it is refer a variable in outer query then it is called correlated subquery. For Example :
SELECT Name FROM Employee WHERE Employee.Eno = (SELECT Work_IN.Eno FROM Work_IN WHERE Dno =11);
- Scalar subquery : It returns only one row and one column
- Row subquery : It returns multiple columns but only one row
- Table subquery : It retrive multiple rows and multiple columns
Possible ways of writing sub query
The possible ways of writing sub query are :
- By using IN predicate : (BETWEEN, LIKE IS NULL cannot be used with the subqueries).
- By using quantifiers comparison predicates : (ANY, ALL)
- By using exist and not exist
Restrictions for subquery
The following operators cannot be used in between main query and subquery:
- BETWEEN and NOT BETWEEN operator
- LIKE and NOT LIKE operator
- IS NULL and IS NOT NULL operator
The following operators are normally used between query and subquery:
- IN predicate : IN or NOT IN
- Quantified comparison predicates : ANY, ALL, SOME
- EXIST predicates: EXIST and NOT EXIST
|Complete Format of SELECT Command||Operators in SQL with Example|
Incoming search terms:
- dbms nested query examples