Mastering SQL for Data Science: A Comprehensive Guide Day-6

Mastering SQL for Data Science: A Comprehensive Guide 

Day-6

Subquery and Set Operators

v  SubQuery:

A subquery is a form of an SQL statement that appears inside another SQL statement. It is also termed as nested query. The statement containing a subquery is called parent query statement. The parent statement uses the rows returned by the subquery. Subquery is always enclosed within parenthesis. Subquery will be evaluated first followed by the main query.

E.g: select location from dept where dept_no =

    (select dept_no from emp where ename =’jayesh’);

    In this case, subquery will execute first and the main query’s condition will work on subquery’s output. When subquery returns more than one row we have to use operators like any, all, in or not in.

E.g: select location from dept where dept_no in

    (select dept_no from emp where ename like ‘j%’);

 

Ø  Exists/ Not Exist operator:

    The exist operator is usually used with correlated sub queries. This operator enables to test whether a values retrieved by the outer query exists in the results set of the values retrieved by the inner query. If the sub query returns at least one row, the operator returns true. If the value does not exist, it returns false.

    The exist operator ensures that the search in the inner query terminates when at least one match is found. Similarly, the not exists operator enables to test whether a value retrieved by the other query is not a part of the result set of the values retrieved by the inner query.

E.g: select emp_no, ename from emp e 

      Where exists (select * from dept d where e.dept_no=d.dept_no);

 

v  Set Operator:

Set operators combine the results of two queries into single one. The following set operators are available in SQL.

1.    Union

2.    Union all

3.    Intersect

4.    Minus

Rules:

1. The queries which are related by a set operator should have the same number of columns and the corresponding columns, must be of the data types.
2. Such a query should not contain any columns of long data types.
3. The label under which the rows are displayed are those from the first select statement.

1.    Union:

The union operator returns all distinct rows selected by two or more queries.

    E.g: select order_no from order_master

    Union

          Select order_no from order_detail;

 2.    Union all:

The union all operators returns all rows selected by either query including duplicates.

     E.g: select order_no from order_master

                   Union all

          Select order_no from order_detail;

 3.    Intersect:

The intersect operator outputs only rows produced by both the queries intersected. i.e. the output in an intersect clause will include only those rows that are retrieved by both the queries.

E.g: select order_no from order_master

               intersect

    Select order_no from order_detail;

 4.    Minus:

The minus operator outputs the rows produced by the first query, after filtering the rows retrieved by the second query.

E.g: select order_no from order_master

               minus

       Select order_no from order_detail;