By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft SQL Server should use data from one table to select the rows in another table. SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. JOIN keyword used in an SQL statement to query data more than one table.
Type of Join
1. Self Join
2. Join or Equi Join or Inner Join
3. Outer Join
4. Cross Join
For Demonstration I’m created two tables.
-- CTREATED TABLE 1
CREATE TABLE table1
(
[ID] INT,
[Value] VARCHAR(10)
)
--INSERT VALUES INTO TABLE 1
INSERT INTO Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
Screen Shot of Table1
-- CREATE TABLE 2
CREATE TABLE table2
(
[ID] INT,
[Value] VARCHAR(10)
)
--INSERT VALUES INTO TABLE 2
INSERT INTO Table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'
Screen Shot of Table1
SELF JOIN
A SELF JOIN is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table.
Syntax
SELECT [COLUMN_NAME] || [ALL] FROM <TABLE_NAME_ 1> <TABLE_NAME_ALIAS_1>, <TABLE_NAME_ 1> <TABLE_NAME_ALIAS_2> WHERE <condition to match common column name>
Example
/* Self Join*/
SELECT t1.ID, t1.[Value] FROM Table1 t1, Table1 t2 WHERE t1.ID = t2.ID
Screen Shot of Self Join
INNER JOIN
INNER JOIN OR EQUI JOIN OR JOIN returns rows when there is at least one match in both the tables.
Syntax
SELECT [COLUMN_NAME] || [ALL] FROM <TABLE_NAME_ 1> <TABLE_NAME_ALIAS>
INNER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <condition to match common column name>
Example
/* JOIN OR EQUI JOIN OR INNER JOIN */
SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.ID = t2.ID
Screen Shot of Inner Join
OUTER JOIN
There are three different Outer Join methods.
I. LEFT OUTER JOIN
II. RIGHT OUTER JOIN
III. FULL OUTER JOIN
LEFT OUTER JOIN:
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
Syntax:
SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM <TABLE_NAME_1> <TABLE_NAME_1_ALIAS>
LEFT OUTER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <CODITION MATCH COLUMN OF TWO TABLE >
Example
/* LEFT OUTER JOIN */
SELECT * FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID
Screen Shot of Left Outer Join
RIGHT OUTER JOIN:
This join returns all the rows from the right table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
Syntax:
SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM <TABLE_NAME_1> <TABLE_NAME_1_ALIAS>
RIGHT OUTER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <CODITION MATCH COLUMN OF TWO TABLE >
Example
/* RIGHT OUTER JOIN */
SELECT * FROM Table1 t1 RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
Screen Shot of Right Outer Join
FULL OUTER JOIN:
This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.
Syntax:
SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM <TABLE_NAME_1> <TABLE_NAME_1_ALIAS>
FULL OUTER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <CODITION MATCH COLUMN OF TWO TABLE >
Example
/* FULL OUTER JOIN */
SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
CROSS JOIN:
This join is a Cartesian join that does not necessitate any condition to join. The result set contains records that are multiplication of record number from both the tables. That is every element of column of first table is associated with every element of column of second table.
Syntax:
SELECT [COLUMN NAME | ALL COLUMN] FROM <FIRST_TABLENAME> <FIRST_TABLENAME_ALIAS>
CROSS JOIN <SECOND_TABLENAME> <SECOND_TABLENAME_ALIAS> ON <SQL CLAUSE|KEYWORD|CONDITION>
Example
/* CROSS JOIN */
SELECT * FROM Table1 t1 CROSS JOIN Table2 t2
Using OUTER JOIN and WHERE clause in Join
This is return NULL column values.
LEFT OUTER JOIN with WHERE NULL
Return left table’s NULL values which not common in right table.
/* LEFT OUTER JOIN - WHERE NULL */
SELECT * FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t2.ID IS NULL
Screen Shot
RIGHT OUTER JOIN with WHERE NULL
Return right table’s NULL values which not common in left table.
/* RIGHT OUTER JOIN - WHERE NULL */
SELECT * FROM Table1 t1 RIGHT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t1.ID IS NULL
FULL OUTER JOIN with WHERE NULL
Return NULL values from left and right table which not common.
/* FULL OUTER JOIN - WHERE NULL */
SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID WHERE t1.ID IS NULL OR t2.ID IS NULL
Leave Comment