Joins and subqueries.

Join syntax.

Join type	Oracle syntax	ANSI syntax
Cartesian	SELECT * FROM table_1, table_2; SELECT * FROM employees, addresses;	SELECT * FROM table_1 CROSS JOIN table_2; SELECT * FROM employees CROSS JOIN addresses;
Inner	SELECT * FROM table_1, table_2 WHERE table_1.field_1 = table_2.field_2; SELECT * FROM employees, addresses WHERE employees.ID = addresses.emp_id;	Natural join SELECT * FROM table_1 NATURAL [INNER] JOIN table_2; If the tables students and grades have common field SID, then the following natural join will join these tables based on the value of this field SELECT * FROM students NATURAL JOIN grades; Note: if there are several columns with the same names in both tables, then the natural join will join the rows with all values in these columns equal.
join using SELECT * FROM table_1 [INNER] JOIN table_2 USING (columns); Again, we assume that both tables departments and locations have column loc_id SELECT * FROM locations JOIN departments USING (loc_id); Note: even if these two tables have other columns with the same name, then the join will be performed only against this particular column.
join on SELECT * FROM table_1 [INNER] JOIN table_2 ON conditions; where conditions is usual condition clause just like the one we can have in the `where` clause. This syntax does not require the tables have columns with the same names. SELECT * FROM employees JOIN addresses ON ID = emp_id;
Outer	The inner join returns only the matched rows from both tables. Sometimes, we might need to see even rows that don't have a match in the other table. For example, we would like to see the list of all courses offered the next semester and the names of the students registered for them even if there are no students registered form them yet. Thus, we need to see rows from the table courses even if there is no match for them in the table registration.
SELECT columns FROM table_1, table_2 WHERE table_1.field = table_2.field (+); SELECT course, student FROM courses, registration WHERE courses.cid = registration.cid (+);	Left outer join The left outer join returns all (matched and unmatched) rows from the table on the left of the JOIN clause and only the matched rows from the other table. SELECT columns FROM table_1 LEFT [OUTER] JOIN table_2 ON conditions; SELECT course, student FROM courses LEFT OUTER JOIN registration ON courses.cid = registration.cid; SELECT columns FROM table_1 LEFT [OUTER] JOIN table_2 USING (columns); SELECT course, student FROM courses LEFT JOIN registration USING (cid); SELECT columns FROM table_1 NATURAL LEFT [OUTER] JOIN table_2; SELECT course, student FROM courses NATURAL LEFT JOIN registration;
Right outer join A right outer join is a join between two tables that returns rows based on matching condition, as well as unmatched rows from the table on right of the JOIN clause. SELECT columns FROM table_1 RIGHT [OUTER] JOIN table_2 ON conditions; SELECT course, student FROM registration RIGHT OUTER JOIN courses ON courses.cid = registration.cid; SELECT columns FROM table_1 RIGHT [OUTER] JOIN table_2 USING (columns); SELECT course, student FROM registration RIGHT JOIN courses USING (cid); SELECT columns FROM table_1 NATURAL RIGHT [OUTER] JOIN table_2; SELECT course, student FROM registration NATURAL RIGHT JOIN courses;
Oracle non-ANSI syntax doesn't allow us to do that. In other words we cannot use two `(+)` signs. Although, we can use the UNION clause together with both left and right joins: SELECT course, student FROM courses, registration WHERE courses.cid = registration.cid (+); UNION SELECT course, student FROM courses, registration WHERE courses.cid(+) = registration.cid;	Full outer join A full outer join returns rows based on matching condition, as well as unmatched rows from both left and right tables. SELECT columns FROM table_1 FULL [OUTER] JOIN table_2 ON conditions; SELECT course, student FROM registration FULL OUTER JOIN courses ON courses.cid = registration.cid; SELECT columns FROM table_1 FULL [OUTER] JOIN table_2 USING (columns); SELECT course, student FROM registration FULL JOIN courses USING (cid); SELECT columns FROM table_1 NATURAL FULL [OUTER] JOIN table_2; SELECT course, student FROM registration NATURAL FULL JOIN courses;

Join type

Oracle syntax

ANSI syntax

Cartesian

SELECT * 
  FROM table_1, table_2;

SELECT * 
  FROM employees, addresses;

SELECT *
  FROM table_1 CROSS JOIN table_2;

SELECT *
  FROM employees CROSS JOIN addresses;

Inner

SELECT *
  FROM table_1, table_2
 WHERE table_1.field_1 = table_2.field_2;

SELECT *
  FROM employees, addresses
 WHERE employees.ID = addresses.emp_id;

Natural join

SELECT *
  FROM table_1 NATURAL [INNER] JOIN table_2;

If the tables students and grades have common field SID, then the following natural join will join these tables based on the value of this field

SELECT *
  FROM students NATURAL JOIN grades;

Note: if there are several columns with the same names in both tables, then the natural join will join the rows with all values in these columns equal.

join using

SELECT *
  FROM table_1 [INNER] JOIN table_2 USING (columns);

Again, we assume that both tables departments and locations have column loc_id

SELECT *
  FROM locations JOIN departments USING (loc_id);

Note: even if these two tables have other columns with the same name, then the join will be performed only against this particular column.

join on

SELECT *
  FROM table_1 [INNER] JOIN table_2 ON conditions;

where conditions is usual condition clause just like the one we can have in the where clause. This syntax does not require the tables have columns with the same names.

SELECT *
  FROM employees JOIN addresses ON ID = emp_id;

Outer

The inner join returns only the matched rows from both tables. Sometimes, we might need to see even rows that don't have a match in the other table. For example, we would like to see the list of all courses offered the next semester and the names of the students registered for them even if there are no students registered form them yet. Thus, we need to see rows from the table courses even if there is no match for them in the table registration.

SELECT columns
  FROM table_1, table_2
 WHERE table_1.field = table_2.field (+);

SELECT course, student
  FROM courses, registration
 WHERE courses.cid = registration.cid (+);

Left outer join

The left outer join returns all (matched and unmatched) rows from the table on the left of the JOIN clause and only the matched rows from the other table.

SELECT columns
  FROM table_1 LEFT [OUTER] JOIN table_2
    ON conditions;

SELECT course, student
  FROM courses LEFT OUTER JOIN registration
    ON courses.cid = registration.cid;

SELECT columns
  FROM table_1 LEFT [OUTER] JOIN table_2
    USING (columns);

SELECT course, student
  FROM courses LEFT JOIN registration
 USING (cid);

SELECT columns
  FROM table_1 NATURAL LEFT [OUTER] JOIN table_2;

SELECT course, student
  FROM courses NATURAL LEFT JOIN registration;

Right outer join

A right outer join is a join between two tables that returns rows based on matching condition, as well as unmatched rows from the table on right of the JOIN clause.

SELECT columns
  FROM table_1 RIGHT [OUTER] JOIN table_2
    ON conditions;

SELECT course, student
  FROM registration RIGHT OUTER JOIN courses
    ON courses.cid = registration.cid;

SELECT columns
  FROM table_1 RIGHT [OUTER] JOIN table_2
    USING (columns);

SELECT course, student
  FROM registration RIGHT JOIN courses
 USING (cid);

SELECT columns
  FROM table_1 NATURAL RIGHT [OUTER] JOIN table_2;

SELECT course, student
  FROM registration NATURAL RIGHT JOIN courses;

Oracle non-ANSI syntax doesn't allow us to do that. In other words we cannot use two (+) signs. Although, we can use the UNION clause together with both left and right joins:

SELECT course, student
  FROM courses, registration
 WHERE courses.cid = registration.cid (+);
UNION
SELECT course, student
  FROM courses, registration
 WHERE courses.cid(+) = registration.cid;

Full outer join

A full outer join returns rows based on matching condition, as well as unmatched rows from both left and right tables.

SELECT columns
  FROM table_1 FULL [OUTER] JOIN table_2
    ON conditions;

SELECT course, student
  FROM registration FULL OUTER JOIN courses
    ON courses.cid = registration.cid;

SELECT columns
  FROM table_1 FULL [OUTER] JOIN table_2
    USING (columns);

SELECT course, student
  FROM registration FULL JOIN courses
 USING (cid);

SELECT columns
  FROM table_1 NATURAL FULL [OUTER] JOIN table_2;

SELECT course, student
  FROM registration NATURAL FULL JOIN courses;

Set operators.

Operator	Description	Example
UNION	Returns all unique rows selected by either query.	SELECT first_name, last_name, grade FROM transcript WHERE CID='IST163' UNION SELECT first_name, last_name, grade FROM transcript WHERE CID='IST263';
UNION ALL	Returns all rows including duplicates selected by either query.	SELECT first_name, last_name, grade FROM transcript WHERE CID='IST163' UNION ALL SELECT first_name, last_name, grade FROM transcript WHERE CID='IST263';
INTERSECT	Returns rows selected by both queries.	SELECT first_name, last_name, grade FROM transcript WHERE CID='IST163' INTERSECT SELECT first_name, last_name, grade FROM transcript WHERE CID='IST263';
MINUS	Returns unique rows selected by the first query, but not the rows selected from the second query.	SELECT first_name, last_name, grade FROM transcript WHERE CID='IST163' MINUS SELECT first_name, last_name, grade FROM transcript WHERE CID='IST263';

Assignment: describe what each example query returns.

Subqueries.

A subquery is a query within a query.

Single-row subquery.

Single-row subquery returns only one row of result. A single-row subquery uses a single row operators. For example, the following query returns all employees with the biggest salary.

SELECT * 
  FROM employees
 WHERE salary = (SELECT MAX(salary) FROM employees);

Multiple-row subquery.

Multiple-row subqueries return more than one row of results from the subquery. We cannot use single row operators (like =), and we have to use the IN operator instead. This operator returns true if a value is inside a set and false otherwise. The following query returns all employees from the departments where at least one John works.

SELECT first_name, last_name, dep_id
  FROM employees
 WHERE dep_id IN (SELECT dep_id
                    FROM employees
                   WHERE first_name = 'John');

Other multiple row operators are

Operator	Description	Example
`EXISTS`	The `EXISTS` operator is always followed by a subquery in parenthesis. This operator returns true if the subquery returns at least one row.	The following query returns all employees currently working on at least one project SELECT * FROM employees e WHERE EXISTS (SELECT * FROM projects WHERE e.emp_id = emp_id);
`ANY`	The `ANY` or `SOME` operators are used to compare a value to each value in a list or subquery. These operators always must be preceded by the comparison operators (`=`, `!=`, `<`, `>`, `<=`, or `>=`).	The query returns all information about employees working for the departments with dep_id less or equal to 12, 23, or 43. SELECT * FROM employees WHERE dep_id <= ANY (12, 23, 43);
`ALL`	The `ALL` operator is used to compare a value to every value in a list or subquery. The `ALL` operator always must be preceded by the comparison operators (`=`, `!=`, `<`, `>`, `<=`, or `>=`).	The query returns all information about the employees working for the departments with dep_id strictly greater than the dep_id of all departments where any John is working. SELECT * FROM employees WHERE dep_id > ALL (SELECT dep_id FROM employees WHERE first_name='John');
`SOME`	Same as `ANY`.	See `ANY`.

Correlated subqueries.

A correlated subquery is a subquery that references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed in the parent statement. For example, the following statement returns the highest-paid employee(s) from each department.

SELECT first_name, last_name, salary
  FROM employees e
 WHERE salary = (SELECT MAX(salary)
                   FROM employee m
                  WHERE m.emp_id = e.emp_id)
 ORDER BY last_name, first_name;

Scalar subquery.

A scalar subquery returns exactly one column value from one row. Scalar subqueries can be used in most places where we can use a column name or expression, such as

inside a single-row function as an argument,
in the VALUES clause of an INSERT statement,
in an ORDER BY clause,
in a WHERE clause,
in a CASE expression
and in a SELECT clause.

Scalar subqueries cannot be used in GROUP BY and HAVING clauses.

Examples:

in a CASE expression

SELECT city, country_id,
       (CASE WHEN country_id IN (SELECT country_id FROM countries WHERE country_name='India')
             THEN 'Indian'
             ELSE 'Non-Indian'
        END) "India?"
  FROM locations
 WHERE city LIKE 'B%';

in a SELECT clause

SELECT dep_name, dep_id
       (SELECT MAX(salary) FROM employees e WHERE e.dep_id=d.dep_id) MAXSAL
  FROM departments d;

in SELECT and WHEREclauses.

SELECT dep_name, manager_id, 
       (SELECT last_name FROM employees WHERE emp_id = d.manager_id) "Manager"
  FROM departments d
 WHERE manager_id IS NOT NULL
       AND
       ( (SELECT country_id 
            FROM locations l 
           WHERE d.location_id = l.location_id
          )
          IN
          (SELECT country_id
             FROM countries
            WHERE country_name = 'United States of America'
                  OR
                  country_name = 'Canada'
          )
       );

in an ORDER BY clause. Using subqueries in the ORDER BY clause we can sort the list of the result rows by a field not included in the output.

SELECT country_id, city_name, state_province
  FROM locations l
 ORDER BY (SELECT country_name FROM countries c WHERE c.country_id = l.country_id);

Subqueries in other DML statements.

Subqueries can be used in DML statements such as INSERT, UPDATE, DELETE, and MERGE. Here are several examples:

To update the salary of all employees to the maximum salary in the corresponding department:

UPDATE employees e
   SET salary = ( SELECT MAX(salary) FROM employees m WHERE m.dep_id = e.dep_id );

To delete the records of employees whose salary is below the average salary in the department:

DELETE FROM employees e
 WHERE salary < ( SELECT MAX(salary) FROM employees m WHERE m.dep_id = e.dep_id );

To insert records to a table from another table:

INSERT INTO employees_archive SELECT * FROM employees

To insert a specific value based on values from a table:

INSERT INTO departments (dep_id, dep_name)
     VALUES ( SELECT MAX(dep_id)+10 FROM departments, 'EDP');

We can also use a subquery in the INSERT, UPDATE, and DELETE statements in place of the table name:

DELETE FROM ( SELECT * FROM departments WHERE dep_id < 20 )
 WHERE dep_id = 10;

The subquery can also have an optional WITH clause:

WITH READ ONLY specifies that the subquery cannot be updated;
WITH CHECK OPTION specifies that the rows not included in the subquery result cannot be updated.