The SQL FULL JOIN operator can greatly simplify certain query tasks. However, no

Question

The SQL FULL JOIN operator can greatly simplify certain query tasks. However, not every relational database management system, such as MySQL, implements FULL JOIN. Given the following partial ERD showing the BOOKS and ORDER_ITEMS tables, discuss how queries generating results equivalent to the FULL JOIN operator can be crafted using alternative commands, and provide an example SQL query for the given FULL JOIN example, using the tables provided. Your answer should both characterize the general approach for replacing the FULL JOIN Operator and the specific SQL query to replace it in order to generate an equivalent result set. Pay special attention to cases where a FULL JOIN would return duplicate rows which might be eliminated by an alternative solution, and discuss circumstances in which this would and would not be acceptable.

The BOOKS table contains a list of all books available for sale through the store. Records exist for books which have previously been ordered, but are no longer available (no record in the BOOKS table). Records also exist for books which are available for purchase, but which have never been ordered (no record in the ORDER_ITEMS table). Finally, records exist for books which have been ordered previously, and which are currently available for purchase (one or many ORDER_ITEMS.BOOKID values correspond to a BOOKS.BOOK_ID value).
SELECT Books.Title, Order_Items.BookID
FROM Books
FULL OUTER JOIN Order_Items
ON Books.BookID=Order_Items.BookID
ORDER BY Books.Title;

Books PK BookID ISBN Title PubDate FK1 PublD Cost Retail publishes Published by HOE Category Order Items has s of PK, FK1 Book ID PK, FK2 Order ID ltemNum Quantity

Explanation / Answer

Full join obtains duplicate results, one method to simulate a full join is to take the union of two outer joins.

Union removes dulicate result-set.

Union :

The "UNION" operator in SQL is used to combine the result-set of two or more SELECT statements.

However, each SELECT statement within the UNION must have the same number of columns with similar data types, also the columns in each SELECT statement must be in the same order.

For Example:
SELECT col(s) FROM tbl1
UNION
SELECT col(s) FROM tbl2;

Answer:

select Books.Title as bookTitle, Order_Items.BookID as orderItem from Books

left outer join Order_Items on Books.BookID=Order_Items.BookID ORDER BY Books.Title

union

select Books.Title as bookTitle, Order_Items.BookID as orderItem from Books

right outer join Order_Items on Books.BookID=Order_Items.BookID

ORDER BY Books.Title orderby bookTitle asc;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.