In the era of big data, there is a ton of information stored in each company’s database. Foot Locker, for example, has information on each customer, product, supplier, and order. All of this information needs to be stored in their database, which includes many tables like the customer table, product table, supplier table, order table, order detail table, and so on. With the help of SQL Join, we can connect different tables based on their related columns.
There are many different types of Joins in SQL, and it can be confusing at first. In this article, I will explain each Join and the difference among them. Below is a diagram of different Joins, together with the SQL Union, which can be confused with SQL Joins.
SQL Joins
Left (Outer) Join
Left Outer Join is also known as Left Join. Suppose there are two tables: tableA and tableB. Left Join will return all rows from TableA (left) and the matching rows from TableB (right). The output of the operation will contain at least all rows in the left table and the matching rows from the right table.
Right (Outer) Join
Right Outer Join is also known as Right Join. Right Join will return all rows from the right table (TableB) and the matching rows from the left table (TableA). The output of the operation will contain at least all rows from the right table and the matching rows from the left table. Right Join is exactly the opposite of Left Join. Therefore, if you switch TableA and TableB in Right Join, you will get the same output as for Left Join.
Full (Outer) Join
Full Outer Join is also known as Full Join. Full Join will return all rows in the left table and all rows in the right table, including all matching rows in both tables. The columns of the Full Join are TableA columns plus TableB columns. It will show all records in both tables.
(Inner) Join
Inner Join is also known as Join. Inner Join will return all rows where there is a match in both tables. If there are no matches between the two tables, then they are excluded from the output records.
Cross Join
Cross Join will return the Cartesian Product of the two joining tables. Cross Join doesn’t require a foreign key in order to join two tables. It will generate all possible combinations of two tables. For instance, in the tables below, we need all possible combinations of TableA and TableB to recreate a full set of playing cards.
Self Join
Self Join means joining a table with itself. Different from other Joins, there is no “Self Join” keyword. It’s just a regular “Inner Join”. But it’s better to use an alias to name each table; otherwise, it can be confusing since there are effectively two of the same table. Self Join is commonly used when there is a relationship between rows in the table itself, like an employee-manager relationship.
SQL Union
SQL Union is different from SQL Joins as Joins are increasing the columns, while Union is increasing the rows. You can think of Union as adding one table below another table. But one requirement for SQL Union would be to have the same structure for both tables—the same number of columns and similar data types in each column.
These are all of the Joins you need to know for combining tables. However, this is just a basic guide to get you started with SQL Joins. If you want to master SQL Joins, I recommend practicing different Joins and learning as you go.
Reference
https://stackoverflow.com/questions/17759687/cross-join-vs-inner-join-in-sqlhttps://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join
https://stackoverflow.com/questions/3362038/what-is-self-join-and-when-would-you-use-it