Introduction
In this blog, I will explain the SQL JOIN Statements. JOIN statements are used to relate one or more tables in SQL server. Two or more tables combine to get values.
Types
1. INNER JOIN
2. OUTER JOINS
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
3. SELF JOIN
4. CROSS JOIN
INNER JOIN
The INNER JOIN keyword is used to select records that return matching values or common values in both tables.
Syntax
- SELECT TB1.NAME1,TB2.SAL FROM TABLE1 TB1 INNER JOIN TABLE2 TB2 ON TB1.ID=TB2.ID;
OUTER JOINS
1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. FULL OUTER JOIN
LEFT OUTER JOIN
The LEFT OUTER JOIN keyword is used to return all values from the left side table (TABLE1) and the matched values from the right side table (TABLE2).
Syntax
- SELECT *FROM TABLE1 TB1 LEFT OUTER JOIN TABLE2 TB2 ON TB1.ID=TB2.ID;
RIGHT OUTER JOIN
The RIGHT OUTER JOIN keyword is used to returns all values from the right side table (TABLE2) and the matched values from the left side table (TABLE1).
Syntax
- SELECT *FROM TABLE1 TB1 RIGHT OUTER JOIN TABLE2 TB2 ON TB1.ID=TB2.ID;
FULL OUTER JOIN
The FULL OUTER JOIN keyword used to returns all values from the left side table (TABLE1) and right side table (TABLE2).
Syntax
- SELECT *FROM TABLE1 TB1 FULL OUTER JOIN TABLE2 TB2 ON TB1.ID=TB2.ID;
SELF JOIN
SELF JOIN is a regular join, but the table is joined with itself. It compares values in a column and other values in the same column and the same table.
Syntax
- SELECT TB1.ID,TB2.FNAME FROM TABLE1 TB1,TABLE1 TB2 WHERE TB1.ID=TB2.ID