Question

This problem is from Chapter 5 of A Guide to SQL ninth addition.

9. For each item, list the item number, description, units on hand, order number, and number of units ordered. All items should be included in the results. For those items that are currently not on order, the order number and number of units ordered should be left blank. Order the results by item number.

All answers I have come up with use the full outer join, however, our professor is asking us to use SQLITE which does not support this command. Is there another solution?

Answer 1

Since you have come with all the answers with Full Outer Join.

But the problem is SQLITE doesn't support this command.

Solution: The way to solve your problem is to implement FULL OUTER JOIN using UNION ALL and LEFT JOIN

commands available in SQLITE.

Lets see an Example on how to implement this::

// We have created a table named "dogs"

CREATE TABLE dogs (

    type       TEXT,

    color TEXT

);

// Now we are inserting the values into the table "dogs"

INSERT INTO dogs(type, color) VALUES('Hunting','Black'), ('Guard','Brown');

// similarly create and insert data into the cats table

CREATE TABLE cats (

    type       TEXT,

    color TEXT

);

INSERT INTO cats(type,color) VALUES('Indoor','White'), ('Outdoor','Black');

// Using of FULL OUTER JOIN

// The following statement uses the FULL OUTER JOIN clause to query data from the dogs and cats tables.

SELECT * FROM dogs

FULL OUTER JOIN cats

    ON dogs.color = cats.color;

// Result of above executed Query

Type	Color	Type	Color
Hunting	Black	Outdoor	Black
Guard	Brown	NULL	NULL
NULL	NULL	Indoor	White

// Now we will try to get same result using UNION ALL and LEFT JOIN commands

SELECT d.type, d.color, c.type, c.color

FROM dogs d

LEFT JOIN cats c USING(color)

UNION ALL

SELECT d.type, d.color, c.type, c.color

FROM cats c

LEFT JOIN dogs d USING(color)

WHERE d.color IS NULL;

// Now we will see how this query works.

• Because SQLilte does not support the RIGHT JOIN clause, we use the LEFT JOIN clause in the second SELECT statement instead and switch the positions of the cats and dogs tables.
• The UNION ALL clause retains the duplicate rows from the result sets of both queries.
• The WHERE clause in the second SELECT statement removes rows that already included in the result set of the first SELECT statement.

Hence in this way you can change all your queries to get your required result in SQLITE.