SQL and its various kinds of joins

SQL stands for Structured Query Language. It’s a language that used to design relational database architects. In an SQL database like MySQL, Sybase, Oracle, or IBM DM2 – SQL executes queries, retrieves data, and edits data by updating, deleting, or creating new records. It’s a lightweight, declarative language that does a lot of heavy lifting for the relational database, acting as a database’s version of a server-side script. SQL databases remain popular because they fit naturally into many venerable software stacks, including the LAMP and Ruby-based stacks. These databases are well understood and widely supported, which can be a major advantage if you run into problems. SQL is a special-purpose programming language designed for managing information in a relational database management system (RDBMS).

SQL Joins

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Types of SQL Joins –

(INNER) JOIN

Returns records that have matching values in both tables. In this kind of a JOIN, we get all records that match the condition in both tables, and records in both tables that do not match are not reported. In other words, INNER JOIN is based on the single fact that: only the matching entries in both the tables should be listed.

OUTER JOIN

OUTER JOIN retrieves. Either, the matched rows from one table and all rows in the other table Or, all rows in all tables (it doesn’t matter whether or not there is a match). In other words, OUTER JOIN is based on the fact that: ONLY the matching entries in ONE OF the tables (RIGHT or LEFT) or BOTH of the tables(FULL) should be listed.

LEFT (OUTER) JOIN

Return all records from the left table, and the matched records from the right table. This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

RIGHT (OUTER) JOIN

Return all records from the right table, and the matched records from the left table. This JOIN returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

FULL (OUTER) JOIN

Return all records when there is a match in either the left or right table. This JOIN combines LEFT OUTER JOIN and RIGHT OUTER JOIN. It returns rows from either table when the conditions are met and returns a NULL value when there is no match.

NATURAL JOIN

It is based on the two conditions-

This seems to be more of theoretical in nature and as a result (probably) most DBMS don’t even bother supporting this.

CROSS JOIN

It is the Cartesian product of the two tables involved. The result of a CROSS JOIN will not make sense in most of the situations. Moreover, we won’t need this at all (or needs the least, to be precise).

SELF JOIN

It is not a different form of JOIN, rather it is a JOIN (INNER, OUTER, etc) of a table to itself.

This post covered the basic types of SQL joins and variants on those basic types. Joins are what make a relational database relational, so if you haven’t mastered the different types of SQL joins available, you’ll want to do so. Thanks for reading! You can share your experiences with us. Thank you!