Left join (SQL) - example, detailed description, usage errors

In any real relational database, all information is distributed in separate tables. Many of the tables have fixed relationships with each other fixed in the diagram. However, using Sql queries, it’s quite possible to build a connection between data that is not inherent in the scheme. This is accomplished by performing the join join operation, which allows you to build relationships between any number of tables and join even seemingly disparate data.

This article will discuss specifically the left outer join. Before starting to describe this type of connection, we will add some tables to the database.

Preparation of the necessary tables

Suppose our database contains information about people and their real estate. The basic information is based on three tables: Peoples (people), Realty (real estate), Realty_peoples (table with relations, which of the people which property belongs to). Suppose the following people data are stored in tables:

Real estate:

By relationship people - real estate:

Left join (Sql) - description

The left join has the following syntax:

And it looks like this:

And this expression is translated as “Select all the rows from table A without exception, and from the table B, output only rows matching the predicate. If in table B there were no pairs for the rows of table A, then fill the resulting Null columns with values. ”

Most often, when performing a left join, ON is indicated, USING is used only when the names of the columns on which it is planned to make a join match.

Left join - use cases

Using the left connection, we can see if all the people from the Peoples list have real estate. To do this, execute the following query example in left join sql:

And we get the following result:

As you can see, Ivanova Daria, Pugin Vladislav and Annina Lyubov have no registered property rights.

And what would we get using the Inner join inner join? As you know, it excludes mismatched lines, so three people from our final sample would simply drop out:

It would seem that the second option also meets the conditions of our task. However, if we continue to join more and more tables, three people from the result will already irrevocably disappear. Therefore, in practice, when joining multiple tables, Left and Right joins are much more often used than Inner join.

We continue to consider examples with left join sql . Let's attach the table with the addresses of our real estate objects:

Now we get not only the type of law, but also the addresses of real estate:

Left join - typical usage errors: incorrect table order

There are two main mistakes made with a left outer join of tables:

1. The order of the tables that caused the data loss was incorrectly selected.
2. Errors when using Where in a query with joined tables.

Consider the first error. Before solving any problem, you should clearly understand what exactly we want to get in the end. In the example considered above, we brought everyone to a single person, but absolutely lost information about the object at number 2, which did not find an owner.

If we rearranged the tables in the query and started with “... From Realty left join Peoples ...”, then we would not lose a single property, which cannot be said about people.

However, you should not, frightened by the left connection, go over to the full external one, which will include as a result both matching and not matching lines.

Indeed, the sample size is often very large, and the extra data is really useless. The main thing is to figure out what you want to get in the end: all people with a list of their real estate, or a list of all real estate with their owners (if any).

Left join - typical usage errors: correct query when setting conditions in Where

The second error is also associated with data loss, and it is not always immediately obvious.

Let’s get back to the query when, using the left connection, we received data on all people and their real estate. Recall the following example using left join sql:

Suppose we want to clarify the request and not display data where the type of law is “Property”. If we just add, using left join sql, an example with the following condition:

...

we’ll lose data on people who have no real estate, because an empty Null value is not compared in this way:

To prevent errors for this reason, it is best to set a selection condition immediately upon connection. We suggest considering the following example with left join sql.

The result will be as follows:

Thus, having executed an example simple with left join sql, we got a list of all the people, additionally listing which of them have real estate in shared / joint ownership.

As a conclusion, I would like to emphasize once again that the selection of any information from the database should be treated responsibly. Using left join sql, many of the nuances were revealed to us by a simple example, the explanation of which is one - before starting even making an elementary query, we need to carefully understand what exactly we want to get in the end. Good luck