Question

Structure of the ORDERS table / Structure of the CUSTOMERS table

Which of the following queries will return the same results as the following SQL statement?
SELECT c.customer#, lastname, firstname, order#
FROM customers c, orders o
WHERE c.customer# = o.customer#;

a) SELECT customer#, lastname, firstname, order#
FROM customers
JOIN orders ON customers.customer# = orders.customer#;

b) SELECT customer#, lastname, firstname, order#
FROM orders
JOIN customers ON customers.customer# = orders.customer#;

c) SELECT customer#, lastname, firstname, order#
FROM customers, orders
WHERE customer# = orders.customer#;

d) SELECT customer#, lastname, firstname, order#
FROM customers
WHERE customer# = (SELECT customer# FROM orders);

Answer 1

Queries a), b), and c) will return the same results as the original query because they all utilize the correct inner join structure. Option d) is not equivalent because it uses a subquery instead of joining the two tables on the customer# field.

Step-by-step explanation:

The SQL query provided in the question is a classic example of an inner join between the CUSTOMERS table and the ORDERS table using the older implicit join syntax. It selects columns customer#, lastname, firstname, and order# from the two tables where the customer# fields match. Among the options provided, the query that will return the same results is:

a) SELECT customer#, lastname, firstname, order#
FROM customers
JOIN orders ON customers.customer# = orders.customer#;

This is the modern syntax for an inner join, explicitly stating the join condition. Although option b) uses the same syntax, the sequence of tables is reversed, which does not affect the outcome of an inner join. Therefore, option b) will also return the same results. Option c) uses the same implicit join syntax as the original query and will also return equivalent results. Option d), however, uses a subquery and will not produce the same result set, because it does not properly join the two tables on the customer# field to include the order# information.