LEFT | RIGHT joins (SQL Operations)

To construct SQL queries for LEFT JOIN and RIGHT JOIN with the given "Publishers Table" and "Titles Table", the common column `pub_id` in both tables serves as the key for the join. Here’s how you can structure these queries:

1. LEFT JOIN Query:
   • The LEFT JOIN will return all records from the "Publishers Table" (left table) and the matched records from the "Titles Table" (right table). If there is no match, the result will include NULL values for the columns from the "Titles Table".
   • Query:

     SELECT Publishers.pub_id, Publishers.pub_name, Titles.title_id, Titles.price, Titles.pubdate
     FROM Publishers
     LEFT JOIN Titles ON Publishers.pub_id = Titles.pub_id;
     

   • In this query, it selects `pub_id` and `pub_name` from the Publishers Table and `title_id`, `price`, and `pubdate` from the Titles Table. The query ensures that all publishers are listed, regardless of whether they have a title in the Titles Table.
2. RIGHT JOIN Query:
   • The RIGHT JOIN will return all records from the "Titles Table" (right table) and the matched records from the "Publishers Table" (left table). If there is no match, the result will include NULL values for the columns from the "Publishers Table".
   • Query:

     SELECT Publishers.pub_id, Publishers.pub_name, Titles.title_id, Titles.price, Titles.pubdate
     FROM Publishers
     RIGHT JOIN Titles ON Publishers.pub_id = Titles.pub_id;
     

   • This query selects the same columns as the LEFT JOIN. However, it ensures that all titles are listed, including those that may not have a corresponding publisher in the Publishers Table.

These queries are instrumental in scenarios where you need to understand the relationship between publishers and their titles, especially in cases where there are publishers without titles or titles without publishers. The LEFT JOIN is useful for a complete list of publishers and their associated titles, if any, while the RIGHT JOIN provides a full list of titles, alongside their publishers if available.

SELECT pub_ID, pub_name, title_id 
FROM publishers LEFT OUTER JOIN Titles 
ON publishers.pub_ID = titles.pub_ID