SQL Query - Intermediate

Overview:

We saw in the previous blog about the basic SQL queries and in this section I'll cover the important query concept called Joins.  In real life scenario,most of the time we'll pull data from various source by joining the matching conditions.

There are 6 different types of Joins in Oracle (from 10G onwards) and they are

1. Equi and Non-Equi joins
2. Inner Join
3. Outer joins (This consists of 3 different joins)
1. LEFT 
2. RIGHT 
3. FULL
4. Cross joins or Cartesian Product 
5. Self joins
6. Partition outer joins

1) Equi and Non-Equi joins

An equi-join is a join where the join condition uses the equal to (=) operator to match the rows from two  ore more tables. When a join condition uses any other operator other than '=' to relate the rows of two or more tables, the join is called a non-equi-join.

--Bringing data from two tables using Equi-Join
SELECT
  EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  SALARY,
  MANAGER_ID,
  DEPARTMENT_ID,
  DEPARTMENT_NAME
FROM
  EMPLOYEES,   DEPARTMENTS
WHERE
  EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID

RESULT: Error
This statment will throw the following error  :ORA-00918: column ambiguously defined

REASON:
DEPARTMENT_ID in the select column doesn't know which table we are referring to as the department id exist in both the tables.


SOLUTION:
in this situation as well as its a best practice to specify the table name in front of the column name like the example below:

SELECT
  EMPLOYEES.EMPLOYEE_ID,
  EMPLOYEES.FIRST_NAME,
  EMPLOYEES.LAST_NAME,
  EMPLOYEES.SALARY,
  EMPLOYEES.MANAGER_ID,
  EMPLOYEES.DEPARTMENT_ID,
  DEPARTMENTS.DEPARTMENT_NAME
FROM
  EMPLOYEES,   DEPARTMENTS
WHERE
  EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID

RESULT:  Successful
All matching records are displayed.

ALTERNATIVE:
The statement above can also be return by supplying an alias name to the table, this way you don't need to type the full table name

SELECT
  E.EMPLOYEE_ID,
  E.FIRST_NAME,
  E.LAST_NAME,
  E.SALARY,
  E.MANAGER_ID,
  E.DEPARTMENT_ID,
  D.DEPARTMENT_NAME
FROM
  EMPLOYEES E,   DEPARTMENTS D
WHERE
  E.DEPARTMENT_ID=D.DEPARTMENT_ID


--Adding additional queries to the join
SELECT
  E.EMPLOYEE_ID,
  E.FIRST_NAME,
  E.LAST_NAME,
  E.SALARY,
  E.MANAGER_ID,
  E.DEPARTMENT_ID,
  D.DEPARTMENT_NAME
FROM
  EMPLOYEES E,   DEPARTMENTS D
WHERE
  E.DEPARTMENT_ID=D.DEPARTMENT_ID
  AND E.SALARY>=8000
ORDER BY E.FIRST_NAME, E.SALARY


2) Inner joins 
An inner join returns the rows that satisfy the join condition.Each row returned by an inner join contains data from all the tables in the join that matches the criteria.

The difference is how Oracle created execution plan.  Inner Joins are consider faster than Equi Join.

SELECT
  E.EMPLOYEE_ID,
  E.FIRST_NAME,
  E.LAST_NAME,
  E.SALARY,
  E.MANAGER_ID,
  E.DEPARTMENT_ID,
  D.DEPARTMENT_NAME
FROM
  EMPLOYEES E
INNER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
  AND E.SALARY>=8000
ORDER BY E.FIRST_NAME, E.SALARY



SELECT 
  D.DEPARTMENT_NAME,
  L.CITY,
  L.STATE_PROVINCE,
  L.COUNTRY_ID
FROM DEPARTMENTS D INNER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID;


3) Outer joins
An outer join returns the rows that satisfy the join condition and also the rows from one table for which no corresponding rows exist in the other table.


a) LEFT Outer Join

Specifies that the results be generated using all rows from DEPARTMENT. For those rows in DEPARTMENT that don't have corresponding rows in LOCATION table  NULL's are returned in the result set for the LOCATIONS table's columns.

SELECT 
  D.DEPARTMENT_NAME,
  L.CITY,
  L.STATE_PROVINCE,
  L.COUNTRY_ID
FROM DEPARTMENTS D LEFT OUTER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID;

RESULT:
Returns all matching 27 records

b) RIGHT Outer Join
Specifies that the results be generated using all rows from LOCATIONS table. For those rows in LOCATIONS  table that don't have corresponding rows in DEPARTMENT table, NULL's are returned in the result set for the DEPARTMENT table's columns.

SELECT 
  D.DEPARTMENT_NAME,
  L.CITY,
  L.STATE_PROVINCE,
  L.COUNTRY_ID
FROM DEPARTMENTS D RIGHT OUTER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID;

RESULT:
Returns all matching s 43 records including non matching record from Department with NULL as value

c) FULL Outer Join
Specifies that the results be generated using all rows from DEPARTMENTS and LOCATIONS. For those rows in DEPARTMENTS that don't have corresponding rows in LOCATION, NULL's are returned in the result set for the LOCATIONS columns. Additionally, for those rows in LOCATIONS that don't have corresponding rows in DEPARTMENTS , NULL's are returned in the result set for the DEPARTMENTS columns.

SELECT 
  D.DEPARTMENT_NAME,
  L.CITY,
  L.STATE_PROVINCE,
  L.COUNTRY_ID
FROM DEPARTMENTS D FULL OUTER JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID;

4) Cross joins or Cartesian Product
Cross joins are joins without a join condition. Each row of one table is combined with each row of another table. The result is referred to as a Cartesian product.

SELECT  
  E.FIRST_NAME, 
  E.LAST_NAME,
  D.DEPARTMENT_NAME
FROM  EMPLOYEES E CROSS JOIN DEPARTMENTS D;