I this post I would like to write about sub queries. A Sub-query is also a query, which is defined under a main query and embedded within the WHERE clause . It always sends a value to its nearest main query. Sub-query is also know as Inner query or Nested query.
A Sub-query is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Sub-queries must be enclosed within parentheses.
If a Sub-query send single value to its nearest main query then that sub query is called single valued sub query.
If a Sub-query sends multiple values to its nearest main query then that sub query is called multi valued sub query. in this case instead of embedded within the WHERE clause you must use WHERE IN clause.
Sub-queries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.
Syntax of sub query
SELECT * FROM TABLE_NAME WHERE COLUMN_NAME = (SELECT COLUMN_NAME FROM TABLE_NAME WHERE CONDITION)
Example table structure
TABLE NAME – tab_employee.
+-----------+-----------+--------+------------------------+ | EmpNo | LastName | DEPTNO | SALARY | +-----------+-----------+--------+------------------------+ | 111 | Satish | 11 | 8000000 | | 122 | Arjun | 11 | 10000000 | | 133 | Kalyan | 11 | 20000000 | | 111 | Praivnh | 13 | 8000000 | | 122 | Naresh | 82 | 2000000 | | 133 | Pavan | 11 | 20000000 | +-----------+-----------+--------+------------------------+
To display employee details, who are working under sathish deportment we will right something like below shown
SELECT * FROM tab_employee WHERE DEPTNO = (SELECT DEPTNO FROM tab_employee WHERE EmpName = 'Satish')