Question

Run all three queries:

a. SELECT FIRSTNAME, LASTNAME, ORDER#
FROM CUSTOMERS LEFT OUTER JOIN ORDERS USING (CUSTOMER#);

b. SELECT FIRSTNAME, LASTNAME, NVL(ORDER#, ‘No Order Found’) FROM CUSTOMERS LEFT OUTER JOIN ORDERS USING (CUSTOMER#);

c. SELECT FIRSTNAME, LASTNAME, NVL(TO_CHAR(ORDER#), ‘No Order Found’) FROM CUSTOMERS LEFT OUTER JOIN ORDERS USING (CUSTOMER#);

Explain what happened from query a to c.
Which customers were flagged as ‘No Order Found’?

Answer 1

The three SQL queries demonstrate how to list customers and their orders with different handling of NULL values.

Step-by-step explanation:

The student has asked a question related to SQL queries, and specifically about the difference in results when executing three slightly different queries. All queries involve a LEFT OUTER JOIN between the CUSTOMERS and ORDERS tables on the CUSTOMER# field.

The first query (SELECT FIRSTNAME, LASTNAME, ORDER# FROM CUSTOMERS LEFT OUTER JOIN ORDERS USING (CUSTOMER#)) retrieves all customers along with their order numbers if any exist. For customers without orders, NULL will be shown in the ORDER# field.

The second query (SELECT FIRSTNAME, LASTNAME, NVL(ORDER#, 'No Order Found') FROM CUSTOMERS LEFT OUTER JOIN ORDERS USING (CUSTOMER#)) uses the NVL function to replace NULL values in the ORDER# field with the string 'No Order Found'. This makes it clear which customers do not have orders.

The third query (SELECT FIRSTNAME, LASTNAME, NVL(TO_CHAR(ORDER#), 'No Order Found') FROM CUSTOMERS LEFT OUTER JOIN ORDERS USING (CUSTOMER#)) is similar to the second, but it first converts the ORDER# to a character string before the NVL function is applied. This step is unnecessary if ORDER# is already a character or string type, but it is required if ORDER# is a numerical type that cannot be directly compared with a string.

Customers who were flagged as 'No Order Found' are those who do not have corresponding entries in the ORDERS table; in other words, customers who have not placed any orders.