JOIN Basics

A join clause basically used to combine two or more tables. We generally used 3 types of joins. These are: INNER JOINS, OUTER JOINS (LEFT, RIGHT, FULL) & CROSS JOINS.

We will discuss other joins like Natural Join/INNER JOINS, EQUI Join, Self Join.

Every join maintain a principle, which is

JOIN = Cross Product + condition

Natural Join/INNER JOINS

Natural join is an SQL join operation that creates join on the base of the common columns in the tables. To perform natural join there must be one common attribute (Column) between two tables. Natural join will retrieve from multiple relations.

A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):

  • All the common columns
  • Every column in the first (left) table that is not a common column
  • Every column in the second (right) table that is not a common column

Let’s discuss about natural join works…

Syntax –

SELECT attribute_names FROM table1 NATURAL JOIN table2; Or, SELECT attribute_names FROM table1 NATURAL JOIN table2 WHERE condition;

For doing further operations we consider two tables – ‘students’ & ‘games’

Let’s implement NATURAL JOIN operations…

Set 1:

set 2:

set 3:

Syntax 2

SELECT attribute_names FROM table1 INNER JOIN table2 WHERE condition;

                  Let’s consider two tables – ‘employee’ & ‘empinfo’.

Let’s implement INNER JOIN operations…

Let’s see another example:

EQUI Join

EQUI JOIN creates a JOIN for equality or matching column(s) values of the relative tables. EQUI JOIN also creates JOIN by using JOIN with ON and then providing the names of the columns with their relative tables to check equality using equal sign (=).

EQUI Join can be two types –

  • EQUI Joins
  • NON-EQUI Joins

EQUI JOINS

Syntax

SELECT attributes FROM table1 JOIN table2 WHERE condition1 = condition2;

 Let’s see implement of this…

Set 1:

set 2:

NON-EQUI Joins

NON EQUI JOIN performs a JOIN using comparison operator other than equal(=) sign like >, <, >=, <= with conditions.

Syntax:

SELECT *  FROM table_name1, table_name2  WHERE table_name1.column [> |  < |  >= | <= ] table_name2.column;

We consider two tables – ‘employees’ & ‘salaries’.

let’s implement NON-EQUI joins…

set 2:

download notes