A join will allow us to view data from related tables in a single result set.
INNER JOIN:
- In an inner join, Only rows with values satisfying the join condition in the common column are displayed.
- It is default join. So 'INNER' keyword can be ignored.
OUTER JOIN:
- In an outer join, rows are returned even when there are no matches through the JOIN criteria on the second table.
- Outer join is of three types: Left, Right, Full outer joins
LEFT OUTER JOIN:
- A left outer join or a left join returns results from the table mentioned on the left of the JOIN keyword irrespective of whether it finds matches or not.
- If the ON clause matches 0 records from table on the right, it will still return a row in the result—but with NULL in each column.
RIGHT OUTER JOIN:
- A right outer join or a right join returns results from the table mentioned on the right of the JOIN keyword irrespective of whether it finds matches or not.
- If the ON clause matches 0 records from table on the left, it will still return a row in the result—but with NULL in each column.
FULL OUTER JOIN:
- A full outer join will combine results of both left and right outer join. Hence the records from both tables will be displayed with a NULL for missing matches from either of the tables.
CROSS JOIN:
- A cross join also known as cartesian product between two tables joins each row from one table with each row of the other table.
- A cross join does not include 'on' clause.
Two tables are created and values are inserted using following SQL statements.
CREATE TABLE A (ID numeric)
INSERT INTO A VALUES(1)
INSERT INTO A VALUES(2)
INSERT INTO A VALUES(3)
CREATE TABLE B (ID numeric)
INSERT INTO B VALUES(4)
INSERT INTO B VALUES(5)
INSERT INTO B VALUES(6)
Using the above tables, try to answer the following questions.
Questions:
1. What will be output of following query?
SELECT A.* from A inner join B on A.ID=B.ID
(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 0 Rows
2. What will be output of following query?
SELECT A.* from A left outer join B on A.ID=B.ID
(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 4, 5, 6
3. What will be output of following query?
SELECT A.* from A right outer join B on A.ID=B.ID
(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 4, 5, 6
4. What will be output of following query?
SELECT A.* from A full outer join B on A.ID=B.ID
(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 4, 5, 6
5. What will be output of following query?
SELECT A.* from A cross join B
(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 1, 2, 3, 1, 2, 3, 1, 2, 3
Show Answers:
