por JOINs in SQL are used to combine rows from two or more tables based on a common field between them. There are more types of joins in SQL, such as Cross Join or Self Join, but not all of them are supported by all database systems. In this post, we will share with you how Outer Join works in SQL.
How does Outer Join work?
The most common type of JOIN in the relational database model is the SQL Inner Join, which, in practice, is used when you want to connect two tables in a database using a common column. Each record in one table is merged with the corresponding record in the other and those for which the Database Management System (SGBD) cannot find a match.
Faced with this statement, the outer join in SQL not only dumps the data records that meet the selection condition in the two tables (for example, equal values in two columns), but also the remaining tuples of one or the other table.
For example, if we internally relate the STUDENTS table to the EXAMS table we can show the data of all students that were examined, but with this alone we would not be able to know which were the students who, for some reason, were not examined. On the other hand, if we relate them externally, we can show the data of all students, whether they have been examined or not.
If we internally relate the PRODUCTS and SALES tables, we can show the data of all products sold, but we would not be able to know which products were not sold. On the other hand, if we relate these tables externally, we could know which products were not sold.
Types of Outer Join
There are three types of outer join sql. This classification depends on whether the data to be displayed is in the first table, the second table, or both.
left outer join in sql: The data in the table on the left (LEFT) is shown yes or yes. Those in the table on the right are only shown if they have been matched; otherwise, NULL is displayed.
Right Outer Join: The data in the table on the right (RIGHT) is displayed yes or yes. Those in the table on the left are only shown if they have been matched; otherwise, NULL is displayed.
Full Outer Join: all rows from each table are displayed, setting NULL when a match cannot be made.
Where to continue?
We have explained how Outer Join works in SQL and we have presented some examples, but there are many more types of joins. If you want to continue learning about databases, we suggest our Full Stack Big Data, Artificial Intelligence & Machine Learning Bootcamp. With this Bootcamp you will develop extensive knowledge of statistics, taking into account that exploratory data analysis is the first step that every Data Scientist must take. Don’t wait any longer to get started!