JOINs IN SQL SERVER WITH SAMPLE DATA

JOIN FUNDAMENTALS

We can retrieve data from two or more tables based on matching columns between / among the tables in the same database or different databases.

It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to each.

IMPORTANT TYPES OF JOINS are

1. INNER JOIN
2. RIGHT JOIN
3. LEFT JOIN
4. OUTER JOIN
5. RIGHT JOIN WITH WHERE NULL
6. LEFT JOIN WITH WHERE NULL 
7. OUTER JOIN WITH WHERE NULL

-- CREATE A “CAR_TWOWHL”  TABLE

CREATE TABLE CAR_TWOWHL (CAR_CNT INT, TWOWHL_CNT INT,HOUSE_NAME VARCHAR(10))

GO

-- CREATE A “CAR” TABLE:

CREATE TABLE CAR (CAR_CNT INT,HOUSE_NAME VARCHAR(10))

GO

-- INSERT VALUES INTO A CAR_TWOWHL TABLE

INSERT INTO CAR_TWOWHL

VALUES

('1','1','One'),

('0','6','YTS'),

('3','7','Three'),

('9','0','Nine')

GO

-- INSERT VALUES INTO A CAR TABLE

INSERT INTO CAR

VALUES

(0,'ZERO'),

(1,'One'),

(2,'Five'),

(4,'Three'),

(5,'Five'),

(6,'Six'),

(7,'Seven'),

(8,'Eight')

GO

-- VALIDATING A TABLE STRUCTURE WITH VALUES

SELECT * FROM CAR_TWOWHL

GO

SELECT * FROM CAR

GO

--INNER JOIN

n       Match rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data.

n       TABLE : CAR_TWOWHL =CT  &  CAR =C

SELECT *

FROM CAR_TWOWHL CT

INNER JOIN CAR C

ON CT.CAR_CNT = C.CAR_CNT

GO

OUTPUT:

CAR_CNT	TWOWHL_CNT	HOUSE_NAME	CAR_CNT	HOUSE_NAME
0	6	YTS	0	ZERO
1	1	One	1	One

-- RIGHT JOIN

n       Returns all records from the right side table and match records from left side tables.

n       NULL values return where a record exists in the right table but not in the left table.

SELECT CT.*,C.*

FROM CAR_TWOWHL CT

RIGHT JOIN CAR C ON CT.CAR_CNT = C.CAR_CNT

GO

OUTPUT:

CAR_CNT	TWOWHL_CNT	HOUSE_NAME	CAR_CNT	HOUSE_NAME
0	6	YTS	0	ZERO
1	1	One	1	One
NULL	NULL	NULL	2	Five
NULL	NULL	NULL	4	Three
NULL	NULL	NULL	5	Five
NULL	NULL	NULL	6	Six
NULL	NULL	NULL	7	Seven
NULL	NULL	NULL	8	Eight

-- LEFT JOIN

n       Returns all records from the left side table and match records from right side tables.

n       NULL values return where a record exists in the left table but not in the right table.

SELECT CT.*,C.*

FROM CAR_TWOWHL CT

LEFT JOIN CAR C ON CT.CAR_CNT = C.CAR_CNT

GO

OUTPUT:

CAR_CNT	TWOWHL_CNT	HOUSE_NAME	CAR_CNT	HOUSE_NAME
1	1	One	1	One
0	6	YTS	0	ZERO
3	7	Three	NULL	NULL
9	0	Nine	NULL	NULL

-- OUTER JOIN

n       Displays all the matching and non matching rows of the tables

n       Returns all records from both the tables

SELECT *

FROM CAR_TWOWHL CT

FULL OUTER JOIN CAR C ON CT.CAR_CNT = C.CAR_CNT

GO

 

OUTPUT:

CAR_CNT	TWOWHL_CNT	HOUSE_NAME	CAR_CNT	HOUSE_NAME
1	1	One	1	One
0	6	YTS	0	ZERO
3	7	Three	NULL	NULL
9	0	Nine	NULL	NULL
NULL	NULL	NULL	2	Five
NULL	NULL	NULL	4	Three
NULL	NULL	NULL	5	Five
NULL	NULL	NULL	6	Six
NULL	NULL	NULL	7	Seven
NULL	NULL	NULL	8	Eight

-- RIGHT JOIN - WHERE NULL

n       RIGHT JOIN MINUS INNER JOIN

n       Returns unique records in the right side table

SELECT CT.*,C.*

FROM CAR_TWOWHL CT

RIGHT JOIN CAR C ON CT.CAR_CNT = C.CAR_CNT

WHERE CT.CAR_CNT IS NULL

GO

OUTPUT:

CAR_CNT	TWOWHL_CNT	HOUSE_NAME	CAR_CNT	HOUSE_NAME
NULL	NULL	NULL	2	Five
NULL	NULL	NULL	4	Three
NULL	NULL	NULL	5	Five
NULL	NULL	NULL	6	Six
NULL	NULL	NULL	7	Seven
NULL	NULL	NULL	8	Eight

-- LEFT JOIN - WHERE NULL

n       LEFT JOIN minus INNER JOIN

n       Returns unique records in the left side table

SELECT CT.*,C.*

FROM CAR_TWOWHL CT

LEFT JOIN CAR C ON CT.CAR_CNT = C.CAR_CNT

WHERE C.CAR_CNT IS NULL

GO

OUTPUT:

CAR_CNT	TWOWHL_CNT	HOUSE_NAME	CAR_CNT	HOUSE_NAME
3	7	Three	NULL	NULL
9	0	Nine	NULL	NULL

-- OUTER JOIN - WHERE NULL

n       RIGHT JOIN (NULL) + INNER JOIN (NULL)

n       Returns unique records in both the tables

SELECT *

FROM CAR_TWOWHL CT

FULL OUTER JOIN CAR C ON CT.CAR_CNT = C.CAR_CNT

WHERE CT.CAR_CNT IS NULL OR C.CAR_CNT IS NULL

GO

OUTPUT:

CAR_CNT	TWOWHL_CNT	HOUSE_NAME	CAR_CNT	HOUSE_NAME
3	7	Three	NULL	NULL
9	0	Nine	NULL	NULL
NULL	NULL	NULL	2	Five
NULL	NULL	NULL	4	Three
NULL	NULL	NULL	5	Five
NULL	NULL	NULL	6	Six
NULL	NULL	NULL	7	Seven
NULL	NULL	NULL	8	Eight