SQL JOIN Queries – How to use SQL JOINS and What do SQL JOINS mean?

SQL JOIN Queries – How to use SQL JOINS and What do SQL JOINS mean?

The work JOIN basically means to link or connect two entities. In SQL this is used to connect / join two tables.

What are the different types of JOINS and how are they different?

TYPE OF JOIN	DESCRIPTION
INNER JOIN	Returns all rows when there is at least one match in BOTH tables
LEFT JOIN	Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN	Return all rows from the right table, and the matched rows from the left table
FULL JOIN	Return all rows when there is a match in ONE of the tables

 

Example:

Note:

There is an Educational_Purpose_DB on USFLDFBSQL004 where the following queries can be executed

Query:

/* SELECT ALL ROWS FROM STORE_TABLE_A */

SELECT * FROM Store_Table_A

Result:

ID	STORE_FIRST_NAME
1	WAL
2	PUB
3	MA
4	JC
7	BASKIN

 

Query:

/* SELECT ALL ROWS FROM STORE_TABLE_B */

SELECT * FROM Store_Table_B

Result:

ID	STORE_LAST_NAME
1	MART
2	LIX
3	CY
4	PENNY
5	DONALDS
6	DONUTS

 

Query:

/* INNER JOIN */

--SELECT ALL THAT ARE IN COMMON BETWEEN THE TWO TABLES

 

SELECT

--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED

A.ID AS 'PRIMARY KEY IN TABLE A',

B.ID AS 'PRIMARY KEY IN TABLE B',

A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',

B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'

-- FROM TABLE A DOING AN INNER JOIN WITH TABLE B

FROM Store_Table_A AS A INNER JOIN Store_Table_B AS B

--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME

ON A.ID = B.ID

Result:

PRIMARY KEY IN TABLE A	PRIMARY KEY IN TABLE B	STORE INITIAL IN TABLE A	STORE INITIAL IN TABLE B
1	1	WAL	MART
2	2	PUB	LIX
3	3	MA	CY
4	4	JC	PENNY

 

Query:

/* LEFT JOIN */

--SELECT ALL THAT ARE IN LEFT TABLE AND WHATEVER IS COMMON BETWEEN THE TWO TABLES

 

SELECT

--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED

A.ID AS 'PRIMARY KEY IN TABLE A',

B.ID AS 'PRIMARY KEY IN TABLE B',

A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',

B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'

-- FROM TABLE A DOING AN LEFT JOIN WITH TABLE B

FROM Store_Table_A AS A LEFT JOIN Store_Table_B AS B

--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME

ON A.ID = B.ID

Result:

PRIMARY KEY IN TABLE A	PRIMARY KEY IN TABLE B	STORE INITIAL IN TABLE A	STORE INITIAL IN TABLE B
1	1	WAL	MART
2	2	PUB	LIX
3	3	MA	CY
4	4	JC	PENNY
7	NULL	BASKIN	NULL

 

Query:

/* RIGHT JOIN */

--SELECT ALL THAT ARE IN RIGHT TABLE AND WHATEVER IS COMMON BETWEEN THE TWO TABLES

 

SELECT

--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED

A.ID AS 'PRIMARY KEY IN TABLE A',

B.ID AS 'PRIMARY KEY IN TABLE B',

A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',

B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'

-- FROM TABLE A DOING AN RIGHT JOIN WITH TABLE B

FROM Store_Table_A AS A RIGHT JOIN Store_Table_B AS B

--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME

ON A.ID = B.ID

Result:

PRIMARY KEY IN TABLE A	PRIMARY KEY IN TABLE B	STORE INITIAL IN TABLE A	STORE INITIAL IN TABLE B
1	1	WAL	MART
2	2	PUB	LIX
3	3	MA	CY
4	4	JC	PENNY
NULL	5	NULL	DONALDS
NULL	6	NULL	DONUTS

 

Query:

/* FULL JOIN */

--SELECT ALL THAT ARE IN BOTH TABLES AND WHATEVER IS NOT COMMON BETWEEN THE TWO TABLES AS WELL

 

SELECT

--COLUMNS ARE BEING CHOSEN AND ALIAS ARE BEEN ASIGNED

A.ID AS 'PRIMARY KEY IN TABLE A',

B.ID AS 'PRIMARY KEY IN TABLE B',

A.STORE_FIRST_NAME AS 'STORE INITIAL IN TABLE A',

B.STORE_LAST_NAME AS 'STORE INITIAL IN TABLE B'

-- FROM TABLE A DOING AN RIGHT JOIN WITH TABLE B

FROM Store_Table_A AS A FULL JOIN Store_Table_B AS B

--CONDITION BEING THE PRIMARY KEYS SHOULD BE THE SAME

ON A.ID = B.ID

Result:

PRIMARY KEY IN TABLE A	PRIMARY KEY IN TABLE B	STORE INITIAL IN TABLE A	STORE INITIAL IN TABLE B
1	1	WAL	MART
2	2	PUB	LIX
3	3	MA	CY
4	4	JC	PENNY
NULL	5	NULL	DONALDS
NULL	6	NULL	DONUTS
7	NULL	BASKIN	NULL

 

*If the primary key do not exist in the second table [for left, right and full joins], the values will be displayed as NULL