Nested queries in database

A subquery, also known as a nested query or subselect, is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used. ... A subquery must return only one column.

For more information visit https://tutsmaster.org/

1. Nested SubQueries Set Membership Set Comparison Test for Empty Relation Test for The Absence of Duplicate Tuples Presented By Tutsmaster.org

2. A Sub query or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause. 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. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc. Nested SubQueries

3. Subqueries must be enclosed within parentheses. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns. An ORDER BY cannot be used in a subquery , although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery . Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator. The SELECT list cannot include any references to values that evaluate to a BLOB (Binary Large Object), ARRAY, CLOB((Character Large Object), or NCLOB. A subquery cannot be immediately enclosed in a set function. The BETWEEN operator cannot be used with a subquery ; however, the BETWEEN operator can be used within the subquery . The Rules of Nested Subqueris

4. SYNTAX FOR SUBQUERIES

5. It is used to check if value of expression is matching a set of values produced by a sub query. For sub query set membership condition, In Keyword is used. Example SELECT NAME FROM EMPLOYEES WHERE EMPLOYEE IN ( SELECT COMPANY FROM COMPANIES WHERE AVG_SALES > TOTAL_SALES SET MEMBERSHIP

6. It is used to compare value of expression in marching a set produced by a sub query. For sub query set comparison, operation (> and <) are used Example SELECT NAME FROM EMPLOYEES WHERE EMPLOYEE IN (SELECT COMPANY FROM COMPANIES WHERE AVG_SALES > TOTAL_SALES)= (SELECT COMPANY FROM COMPANIES WHERE BRANCH=‘TEXAS’ ) SET COMPARIESON

7. SQL includes a feature for testing whether a sub query has any tuples in its result. EXISTS uses a sub query as a condition, where the condition is True if the sub query returns any rows and False if the sub query does not return any rows; this is a non intuitive feature with few unique uses. EXIST- This operator evaluate to true if the resulting set is not empty. NOT EXIST – This operator evaluated to true if the resulting set is empty. TEST EMPTY RELATIONS

8. SELECT BRANCH FROM COMPANIES WHERE EXISTS ( SELECT CAPITAL FROM COMPANY> 100000) EXCEPT( SELECT NAME FROM COMPANY=‘ENTERPRISE’ ) Example

9. The unique construct returns true if the argument subquery contains no duplicate tuples. SQL includes a feature for testing whether a sub query has any duplicate tuples in its result The UNIQUE construct return the value true if the argument sub query contains no duplicate tuples. Using the UNIQUE construct, we can write the query ‘Find all customer Example SELECT NAME FROM EMPLOYEE WHERE UNIQUE( SELECT SALARY FROM EMPLOYE = 100000; ) TEST FOR THE ABSENCE OF DUPLICATE TUPLES