Reverse Instructional Design — LEFT JOIN in MySQL/MariaDB

Jul 23, 2014 • Luca Cerone

Imagine in your MySQL database you have a table A like this:

id
1
2
3
4

and a table B like this:

id
1
2
5
6

Answer the following questions:

  1. What would be the result of the following query?

SELECT A.id, B.id FROM A LEFT JOIN B ON A.id=B.id ORDER BY A.id;

a) The syntax is incorrect it produces an error

b) The table:

A.id B.id
1 1
2 2
NULL 5
NULL 6

c) The table:

A.id B.id
1 1
2 2
3 NULL
4 NULL

d) The table:

A.id B.id
1 1
2 2
3 NULL
4 NULL
NULL 5
NULL 6

2) What would be the results of the following query (note the ON condition!)?

SELECT A.id, B.id FROM A LEFT JOIN B ON A.id=A.id ORDER BY A.id;

a) The syntax is incorrect it produces an error

b)The table:

A.id B.id
1 1
1 2
1 5
1 6
2 1
2 2
2 5
2 6
3 1
3 2
3 5
3 6
4 1
4 2
4 5
4 6

c) The table:

A.id B.id
1 1
2 2
3 NULL
4 NULL

d)The table:

A.id B.id
1 1
1 NULL
1 NULL
1 NULL
2 NULL
2 2
2 NULL
2 NULL
3 NULL
3 NULL
3 5
3 NULL
4 NULL
4 NULL
4 NULL
4 6