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
|
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
|
/* 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