Joins Simplified: Blog-post Comment Section Case Study

Joins Simplified: Blog-post Comment Section Case Study

Joins is an intriguing method in SQL used to extract useful information from two tables, provided that there is at least one common column between the two tables. Joins can be used as a substitute to sub-queries. The resultant of the joins can be stored in another table or displayed through a view.

  • Inner Join: This type of join takes into consideration only the common records between two tables. For this join to be implemented, there must be a common record to link the two tables.

Syntax: SELECT column_name(s)
                           FROM table1
                           INNER JOIN table2
                           ON table1.column_name = table2.column_name;

Scenario: Consider a blog-post example. Assuming there are two tables. The first one with details of all people visiting the site(Vistor Records- Table 1), whether registered or not . The second table contains the record of all the comments(Comments with Visitor Id- Table 2). If we want the list of all the comments by registered visitors, then inner join has to be used.

  • Outer Join: They can be further sub-categorized into 3 types:

Left Outer Join: This join is inclusive from the point of view of the first table but exclusive from the point of the second table. For this type of join, all the records in the first table is considered along with the matching column records of the second table.

SQL LEFT JOIN

Syntax: SELECT column_name(s)
                           FROM table1
                           LEFT OUTER JOIN table2
                           ON table1.column_name = table2.column_name;

Scenario: Based on the previous example, if the list of all the visitors(registered and unregistered) is needed, irrespective of whether they have posted a comment then left outer join needs to be used.

Right Outer Join: This join is inclusive from the point of view of the first table but exclusive from the point of the second table. For this type of join, all the records in the second table is considered along with the matching column records of the first table.

Syntax: SELECT column_name(s)
                           FROM table1
                           Right OUTER JOIN table2
                           ON table1.column_name = table2.column_name;

Scenario: If all the comments need to be listed along with their respective visitor details then the right outer join needs to be used.

Full Outer Join: This is the most inclusive of joins as all data records are considered.For this type of join, all the records in both the tables are considered.

Syntax: SELECT column_name(s)
                                         FROM table1
                                         FULL OUTER JOIN table2
                                         ON table1.column_name = table2.column_name
                                         WHERE condition;

Scenario: If all the visitors as well as all the comments is required then full outer join needs to be used.

Reference: https://www.w3schools.com/sql/

By: Yogesh Das

 

Leave a Reply

Your email address will not be published. Required fields are marked *