May 22, 2020
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).
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Types of SQL Joins -
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 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.
It is based on the two conditions-
The JOIN is made on all the columns with the same name for equality.
Removes duplicate columns from the result.
This seems to be more of theoretical in nature and as a result (probably) most DBMS don't even bother supporting this.
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).
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!