Skip to main content

SQL Join

What is Join in SQL ?

  • SQL Join used to Combine data from different table based on some common Attribute.

Types of Join

1.Natural Join
2.Cartesian join
3.Inner join or equi join
4.Outer join
—- left outer join
—- Right outer join
—- Full outer join

Consider two table emp1 and departments

EMP1 Table
emp1Departments Table
dept

1.Natural Join

  • The NATURAL JOIN clause is based on all columns in the two tables that have the same name.
  • It selects rows from the two tables that have equal values in all matched columns.
  • If the columns having the same names have different data types, an error is returned.

i.e

SELECT first_name,department_id,department_name
FROM EMP1 NATURAL JOIN Departments;

natural-join

2.Cartesian join

  • A Cartesian join or Cross join is a join of every row of one table to every row of another table.

i.e

SELECT first_name,department_name FROM emp1,departments;
cartesian

3.Inner join or equi join

  • It Returns only the matching row between both the tables and non-matching records are eliminated.
SELECT e.first_name, d.department_id, d.department_name
FROM EMP1 e INNER JOIN Departments d
ON e.Department_id=d.Department_id;

equi-join

4.Outer join

  • Outer join is used to retrieve matching and non-matching rows from whether left table or right table or from both the tables.
  • Outer Joins can be further classified into

1. Left outer join
2. Right outer join
3. Full outer join

1.Right outer join
Written both match it and none match it records right tables

SELECT e.first_name, d.department_id, d.department_name
FROM EMP1 e Right outer JOIN Departments d
ON e.Department_id=d.Department_id;
right-outer-join

2.Left outer join
It return both matching and non-matching records from left table.

SELECT e.first_name, d.department_id, d.department_name
FROM EMP1 e Left outer JOIN Departments d
ON e.Department_id=d.Department_id;
left-ouer-join

3.Full outer join
Is returns both matching and non matching records into left and right table

SELECT e.first_name, d.department_id, d.department_name
FROM EMP1 e FULL outer JOIN Departments d
ON e.Department_id=d.Department_id;
full-outer-join