Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Understanding SQL SELECT Query Statements

By: FYIcenter.com

Part:   1  2  3  4  5  6  7   8 

(Continued from previous part...)

How To Write a Left Outer Join with the WHERE Clause?

If you don't want to use the LEFT OUTER JOIN ... ON clause to write a left outer join, you can use a special criteria in the WHERE clause as "left_table.column = right_table.column(+)". The select statement below is an example of a left outer join written with the WHERE clause:

SQL> set NULL 'NULL'
SQL> SELECT d.department_name, e.first_name, e.last_name 
  2  FROM departments d, employees e 
  3  WHERE d.manager_id = e.employee_id(+);

DEPARTMENT_NAME           FIRST_NAME           LAST_NAME
-------------------- -------------------- --------------
Administration            Jennifer             Whalen
Marketing                 Michael              Hartstein
Purchasing                Den                  Raphaely
Human Resources           Susan                Mavris
Shipping                  Adam                 Fripp
IT                        Alexander            Hunold
......
Treasury                  NULL                 NULL
Corporate Tax             NULL                 NULL
Control And Credit        NULL                 NULL
Shareholder Services      NULL                 NULL
Benefits                  NULL                 NULL
Manufacturing             NULL                 NULL
......

Note that a left outer join may return extra rows from the first (left) table that do not satisfy the join condition. In those extra rows, columns from the second (right) table will be given null values.

The extra rows returned from the left outer join in this example represents departments that have no manager IDs.

How To Name Query Output Columns?

Each column in the query output has a default name. If you don't like the default name, you can specify a new name for any column in the query output by using the AS clause. The following statement shows you a good example:

SQL> SELECT department_id AS ID, MIN(salary) AS Low, 
  2  MAX(salary) AS High, AVG(salary) AS Average 
  3  FROM employees GROUP BY department_id 
  4  HAVING AVG(salary) < 5000;
        ID        LOW       HIGH    AVERAGE
---------- ---------- ---------- ----------
        30       2500      11000       4150
        50       2100       8200 3475.55556
        10       4400       4400       4400

What Is a Subquery?

A subquery is a SELECT statement used as part of the selection criteria of the main SELECT statement. The subquery specified in the WHERE clause will be evaluated repeated on each row of the selection base table. The output of the subquery will be used in the final evaluation of the criteria. Usually, subqueries are used in the following boolean operations:

• "expression IN (subquery)"
• "expression NOT IN (subquery)"
• "EXISTS (subquery)"
• "NOT EXISTS (subquery)"

How To Use Subqueries with the IN Operator?

A subquery can be used with the IN operator as "expression IN (subquery)". The subquery should return a single column with one or more rows to form a list of values to be used by the IN operation. The following tutorial exercise shows you how to use a subquery with the IN operator:

SQL> SELECT first_name, last_name FROM employees 
  2  WHERE department_id IN (
  3    SELECT department_id FROM departments 
  4    WHERE location_id = 1700
  5  );

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King
Neena                Kochhar
Lex                  De Haan
Nancy                Greenberg
Daniel               Faviet
John                 Chen
Ismael               Sciarra
......

How To Use Subqueries with the EXISTS Operator?

A subquery can be used with the EXISTS operator as "EXISTS (subquery)", which returns true if the subquery returns one or more rows. The following statement is a good example of "EXISTS (subquery)". It returns rows from employees table that there are rows existing in the departments table linked to the employees table with location_id = 1700.

SQL> SELECT first_name, last_name FROM employees e 
  2  WHERE EXISTS (
  3    SELECT * FROM departments d 
  4    WHERE e.department_id = d.department_id
  5    AND d.location_id = 1700
  6  );
FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King
Neena                Kochhar
Lex                  De Haan
Nancy                Greenberg
Daniel               Faviet
John                 Chen
Ismael               Sciarra
......

(Continued on next part...)

Part:   1  2  3  4  5  6  7   8