SQL JOIN

JOIN clause is used to combine rows from two or more tables.

INNER JOIN === selects records that have matching values in both tables

SELECT Orders.OrderID, Customers.CustomersID, Orders.OrdersDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

• ( INNER) JOIN: Returns records that have matching values in both tables

• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

The INNER JOIN keyword selects records that have matching values in both tables.

Syntax:-

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

LEFT JOIN :- returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT JOIN:- returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL Outer JOIN

Returns all records when there is a match in left(table1) or right (table2) table records

FULL OUTER JOIN and FULL JOIN both are same

SELECTcolumn_name(s)
FROMtable1
FULLOUTERJOINtable2
ONtable1.column_name = table2.column_name
WHEREcondition;

SELF JOIN

It is a regular Join, but the table is joined with itself

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;