SQL Join

SQL Fundamentals /

Why use SQL Joins?

SQL Join combines the rows from two or more tables to visualize an expected result. The associated tables should have at least one column that contains common data. An alternative to SQL Joining is the usage of subqueries. But, in most cases, the retrieval time of the queries using SQL join is faster than the queries that use a subquery.

Effects of Joining

SQL Join forms a temporary table and visualizes the final output from it. So there is no possibility of alteration in associated tables’ structure and data.

SQL Join has the reverse effect of normalization. During normalization, tables get split into more specific tables with a relationship between them. Also, there will be a minimal amount of duplicate and NULL values in each table. Join invokes the same relationship to construct a temporary table that contains the expected result. For several types of joins, this temporary table is de-normalized in nature containing NULL and duplicate values.

List of Joins

ANSI standard for SQL specifies five types of joins.

  1. Inner Join
  2. Left Outer Join / Left Join
  3. Right Outer Join / Right Join
  4. Full Outer Join
  5. Cross Join

Note:

  • In Oracle we have another join named “Natural Join”. But in Microsoft SQL Server, Natural Join is not available.
  • It’s possible to join a table with “itself” using any joining technique, which is known as Self Joining.   

Brief Definition

  1. Natural Join: Returns only matched records from both tables. But, there is a condition. The heading of joining columns must be the same.
  2. Inner Join: Returns only matched records from the associated tables.
  3. Outer Join:
    • Left Outer Join: Returns matched records from the associated tables and unmatched records only from the table on left side.
    • Right Outer Join: Returns matched records from the associated tables and unmatched records only from the table on right side.
    • Full Outer Join: Returns matched and unmatched records from the associated tables with NULL for unmatched records from both tables.
  4. Cross Join: Returns the Cartesian Product of the rows of the associated tables.

In a nutshell,
Natural Join = Matched, with same column heading.
Inner Join = Matched.
Left Outer Join = Matched plus Left Unmatched.
Right Outer Join = Matched plus Right Unmatched.
Full Outer Join = Matched plus Left Unmatched plus Right Unmatched.
Cross Join = Cartesian Product.

Visual Illustration

Syntax

Natural Join

SELECT columns
FROM table1
NATURAL JOIN table2;

Inner Join

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Left Outer join

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

Right Outer Join

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

Full Outer Join

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

Cross join

SELECT columns
FROM table1
CROSS JOIN table2;

Experiment and Result

Consider the following two tables named EMPLOYEES and DEPARTMENTS

EMPLOYEES

EMPLOYEE_IDEMPLOYEE_NAMEDEPARTMENT_ID
10001LOREM202
10002IPSUM207
10003DOLOR205
10004SIT211
10005AMET201

 

DEPARTMENTS

DEPARTMENT_ID DEPARTMENT_NAME
201 HR
202 SALES
203 FINANCE
204 SERVICE
205 IT
NATURAL JOIN
(Matched Only)

SELECT *
FROM Employees
NATURAL JOIN Departments;

DEPARTMENT_IDEMPLOYEE_IDEMPLOYEE_NAMEDEPARTMENT_NAME
20110005AMETHR
20210001LOREMSALES
20510003DOLORIT
INNER JOIN
(Matched Only)

SELECT E.*,
D.department_name
FROM Employees E
INNER JOIN Departments D
ON E.department_id = D.department_id;

DEPARTMENT_IDEMPLOYEE_IDEMPLOYEE_NAMEDEPARTMENT_NAME
20110005AMETHR
20210001LOREMSALES
20510003DOLORIT
LEFT OUTER JOIN
(Matched+Left Unmatched)

SELECT E.*,
D.department_name
FROM Employees E
LEFT OUTER JOIN Departments D
ON E.department_id = D.department_id;

DEPARTMENT_IDEMPLOYEE_IDEMPLOYEE_NAMEDEPARTMENT_NAME
20110005AMETHR
20210001LOREMSALES
20510003DOLORIT
20710002IPSUMNULL
21110004SITNULL
RIGHT OUTER JOIN
(Matched+Right Unmatched)

SELECT E.*,
D.department_name
FROM Employees E
RIGHT OUTER JOIN Departments D
ON E.department_id = D.department_id;

DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_NAME
201 10005 AMET HR
202 10001 LOREM SALES
205 10003 DOLOR IT
NULL NULL NULL SERVICE
NULL NULL NULL FINANCE
FULL OUTER JOIN
(Matched+Left Unmatched+Right Unmatched)

SELECT E.*,
D.department_name
FROM Employees E
FULL OUTER JOIN Departments D
ON E.department_id = D.department_id;

DEPARTMENT_ID EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_NAME
201 10005 AMET HR
202 10001 LOREM SALES
205 10003 DOLOR IT
NULL NULL NULL SERVICE
NULL NULL NULL FINANCE
207 10002 IPSUM NULL
211 10004 SIT NULL
CROSS JOIN
(Cartesian Product)

SELECT *
FROM Employees
CROSS JOIN Departments;

EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_ID DEPARTMENT_ID DEPARTMENT_NAME
10001LOREM202201HR
10001LOREM202202SALES
10001LOREM202203FINANCE
10001LOREM202204SERVICE
10001LOREM202205IT
10002IPSUM207201HR
10002IPSUM207202SALES
10002IPSUM207203FINANCE
10002IPSUM207204SERVICE
10002IPSUM207205IT
10002DOLOR205201HR
10003DOLOR205202SALES
10003DOLOR205203FINANCE
10003DOLOR205204SERVICE
10003DOLOR205205IT
10004SIT211201HR
10004SIT211202SALES
10004SIT211203FINANCE
10004SIT211204SERVICE
10004SIT211205IT
10005AMET201201HR
10005AMET201202SALES
10005AMET201203FINANCE
10005AMET201204SERVICE
10005AMET201205IT

That’s all for today.

Click here for more learning !

SQL Join Read More »