Oracle SQL Tutorials – Chapter 5

Chapter 5 – Selecting Data From Multiple Tables

• Usage of multiple tables in one query
• Combining tables – JOIN
• Different Types of SQL Join
• Left Outer Join
• Right Outer Join
• SET Operators
• Subqueries

Combining Tables – JOIN

• JOIN is used to select data from multiple tables.
• ALIAS is very important in JOIN operations. It will make our job eaiser.
• The relation between the tables should be well established.
• We must avoid to select unnecessary columns. ( * ) should not be used in queries that are not required.

INNER JOIN

• Let’s write an example of “ INNER JOIN ”.

SELECT department_id, department_name, city

FROM hr.departments d, hr.locations l

WHERE d.location_id=l.location_id;

• Let’s list the Department’s city.

SELECT department_id, department_name, city

FROM hr.departments d

INNER JOIN hr.locations l ON d.location_id=l.location_id;

OR

SELECT department_id, department_name, city

FROM hr.departments d

JOIN hr.locations l USING (location_id);

• Let’s list the ID, name, surname, city and department of employees.

SELECT employee_id, first_name,last_name,city, department_name

FROM hr.employees e

JOIN hr.departments d ON d.department_id = e.department_id

JOIN hr.locations l ON d.location_id = l.location_id;

• Let’s write another example.

SELECT e.first_name,e.last_name, e.salary, j.job_title

FROM hr.employees e

JOIN hr.jobs j ON e.salary

BETWEEN j.min_salary AND j.max_salary;

LEFT OUTER JOIN

• Let’s list the departments of employees.

SELECT first_name,e.last_name, e.department_id, d.department_name

FROM hr.employees e

LEFT OUTER JOIN hr.departments d ON (e.department_id = d.department_id) ;

• Let’s list the departments of employees and non-employed departments.

SELECT first_name,e.last_name, e.department_id, d.department_name

FROM hr.employees e

RIGHT OUTER JOIN hr.departments d ON (e.department_id = d.department_id) ;

CARTESIAN PRODUCT

• Cartesian product is mentioned, in case of where the join condition between two tables is not defined.
• It fetchs all records from the table on the right for each record of the table on the left.

SELECT COUNT(*) FROM hr.regions ;                                  SELECT COUNT(*) FROM hr.locations ;

SELECT *

FROM hr.regions, hr.locations ;

SET OPERATORS

• SET operators are used to merge multiple tables and queries.
• There are 4 types of SET operators. These are;
• UNION
• UNION ALL
• INTERSECT
• MINUS

UNION OPERATOR

• It combines data from two datasets but doesn’t return the same records.

• Here is an example of UNION operator.

SELECT employee_id, job_id

FROM hr.employees

UNION

SELECT employee_id, job_id

FROM hr.job_history;

UNION ALL OPERATOR

• It combines data from two datasets and it also returns the same records.

• Let’s write an example of UNION ALL operator.

SELECT employee_id, job_id, department_id

FROM hr.employees

UNION ALL

SELECT employee_id, job_id, department_id

FROM hr.job_history ;

INTERSECT OPERATOR

• It finds the intersection of two datasets.

• Let’s find the employees which salary is between 3000 and 5000 with intersect operator.

SELECT first_name,last_name,salary FROM hr.employees

WHERE salary BETWEEN 1000 AND 5000

INTERSECT

SELECT first_name,last_name,salary FROM hr.employees

WHERE salary BETWEEN 3000 AND 7500;

MINUS OPERATOR

• This operator returns the difference between two datasets.

• Let’s list the difference between the records in job_history table and records in employees table.

SELECT employee_id, job_id

FROM hr.employees

MINUS

SELECT employee_id, job_id

FROM hr.job_history;

SUB QUERIES

• Sometimes,the result of one query, must be used in another query. In these situaations we use subqueries.
• Subquery runs before the main query.
• The result of the subquery is used by the main query.
• Subquries make it easy to find the desired result.

• Let’s list the employees, whose salary is more than ‘Abel’s salary.

SELECT first_name,last_name, salary

FROM hr.employees

WHERE salary >

(SELECT salary

FROM hr.employees

WHERE last_name = 'Abel');

SUBQUERY VARIATIONS

• Single-Row Subqueries
• Multi-Row Subqueries

SINGLE-ROW SUBQUERIES

• Single-row subquery returns one row.
• They are used with =, <, >, <=, >=, <> operators.

• Let’s list the employees which salary is more than ‘Austin’s salary and in same department with ‘Austin’.

SELECT first_name,last_name, job_id, salary

FROM hr.employees

WHERE job_id =

(SELECT job_id

FROM hr.employees

WHERE last_name = 'Austin')

AND salary >

(SELECT salary

FROM hr.employees

WHERE last_name = 'Austin');

• Group functions can be used with single-row functions.
• Let’s list the employee who has the lowest salary.

SELECT first_name,last_name, job_id, salary

FROM hr.employees

WHERE salary =

(SELECT MIN(salary)

FROM hr.employees);

• Usage of ‘HAVING’ with single-row functions
• Let’s list the employees which salaries are higher than the employee which has the minimum salary in department 50.

SELECT d.department_name,e.department_id, MIN(salary)

FROM hr.employees e,hr.departments d where e.department_id=d.department_id

GROUP BY e.department_id,d.department_name

HAVING MIN(salary) >

(SELECT MIN(salary)

FROM hr.employees

WHERE department_id = 50);

MULTI-ROW SUBQUERIES

• Multi-row subqery returns one or more rows.
• We use them with multi-row comparison operators ( IN, ANY, ALL)

IN OPERATOR

• It checks if the value is exist in the subquery.

SELECT employee_id, first_name,last_name,salary

FROM hr.employees

WHERE salary in

(SELECT MIN(salary)

FROM hr.employees

GROUP BY department_id);

ANY OPERATOR

• If any of the subquery values meet the condition, the ANY operator returns true.
• This operator is used with any of the ‘ =, <, > ’ operators.
• “ =ANY “ means ; IN operator.
• “ <ANY “ means ; smaller than the biggest.
• “ >ANY “ means ; bigger than the smallest.
• Let’s list the employees which has less salary than the employee which salary is the biggest in the IT_PROG position.

SELECT employee_id, last_name, job_id, salary

FROM hr.employees

WHERE salary < ANY

(SELECT salary

FROM hr.employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';

• Let’s list the employees which has more salary than the employee which salary is the lowest in the IT_PROG position.

SELECT employee_id, last_name, job_id, salary

FROM hr.employees

WHERE salary > ANY

(SELECT salary

FROM hr.employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';

ALL OPERATOR

• If all of the subquery values meet the condition, the ALL operator returns true.
• >ALL means ; bigger than the biggest.
• <ALL means ; smaller than the smallest.
• Let’s list the employees which salary is smaller than the smallest salary in ‘IT_PROG’ position.

SELECT employee_id, first_name, last_name, job_id, salary

FROM hr.employees

WHERE salary < ALL

(SELECT salary

FROM hr.employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';

• Let’s list the employees which salary is greater than the biggest salary in ‘IT_PROG’ position.

SELECT employee_id, first_name, last_name, job_id, salary

FROM hr.employees

WHERE salary > ALL

(SELECT salary

FROM hr.employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';