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';