SQL Joins

☞ Following are the topics to be covered:

1. JOINS
2. Cartesian Product(Cross Product)
3. Equi Join
4. Natural Join

JOINS

☞ An SQL JOIN clause is used to combine rows from two or more tables, based on a common field/column between them.

☞ The process of combining data from multiple table is called a JOIN.

☞ While writing a join query, more than one table is considered in FROM clause.

☞ Types of SQL joins are :- Cartesian Product, Equi Join and Natural Join.

CARTESIAN PRODUCT

☞ It is also known as cross product or cross-join.

☞ It is a binary operation.

☞ It is denoted by ✕

☞ Cartesian Product for table1 and table2 = No. of tuples of the table1 ✕ No. of tuples of the table2

☞ Example : If A={1,2} and B={a, b, c}, find A ✕ B
      A ✕ B = {(1,a), (1,b), (1,c), (2,a), (2,b), (2,c)}

☞ Example :

SELECT * FROM teacher , department;

EQUI-JOIN

☞ It is a special type of join where two tables are joined on the basis of common columns having equal values i.e. we use an equality operator.

☞ It shows the common columns from all the participating tables.

☞ Syntax :

SELECT * FROM <table_1>, <table_2>
WHERE <table_1.similar_col_name> = <table_2.similar_col_name>;

☞ Example :

SELECT * FROM teacher t, department d
WHERE t.dept_id = d.dept_id;

NATURAL JOIN

☞ It is a type of EQUI JOIN with a small difference where common columns of associated tables are shown once only in the final result set.

☞ Syntax :

SELECT * FROM <table_1>NATURAL JOIN <table_2>;

☞ Example :

SELECT * FROM Teacher NATURAL JOIN Department;