SQL Server Question Bank: Questions on SQL JOINS

Monday, December 13, 2010

Questions on SQL JOINS

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