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

In any real relational database, all information is distributed on separate tables. Many of the tables have established relationships in the circuit with each other. However, using Sql queries, it is quite possible to make a connection between the data that is not contained in the schema. This is done by performing a join join operation, which allows you to build relationships between any number of tables and to connect even seemingly disparate data.

In this article we will talk specifically about the left external connection. Before we begin to describe this type of connection, we add some tables to the database.

Preparing the necessary tables

For example, in our database there is information about people and their real estate. The basic information is based on three tables: Peoples (people), Realty (real estate), Realty_peoples (the table with the relation, to whom of people what real estate belongs). Suppose, in the tables, the following data for people are stored:

Property:

For relationships, people are real estate:

Left join (Sql) - description

The left connection has the following syntax:

And it looks like this:

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

Most often, when the left connection is performed, ON is specified, USING is used only when the column names for which the connection is scheduled are the same.

Left join - examples of use

With the help of the left connection, we can see if all people on the list of Peoples have real estate. To do this, execute the following 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 rights to real estate.

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 fall out:

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

We continue to consider examples with left join sql. Join the table with the addresses of our real estate:

Now we get not only the form of the right, but also the addresses of real estate:

Left join - typical usage errors: wrong order of tables

The main errors allowed with the left outer join of the tables are two:

1. The order of the tables due to which the data was lost was incorrectly selected.
2. Errors when using Where in a query with merged tables.

Consider the first error. Before solving any problem, it is necessary to clearly understand what exactly we want to get as a result. In the example above, we took out all the people, but absolutely lost information about the object under the number 2, from which the owner was not found.

If we moved the tables in the query places, and started with "... From Realty left join Peoples ..." then we would not lose a single property, which you can not say about people.

However, do not be scared of the left connection, go to the full external, which will include as a result, and coincide, and do not match the line.

After all, the amount of samples is often very large, and the extra data really do not need anything. The main thing is to understand what you want to get as a result: all people with a list of available real estate, or a list of all real estate with their owners (if any).

Left join - typical usage errors: the correctness of the query when setting conditions in Where

The second error is also related to the loss of data, and not always immediately obvious.

Let's return to the query, when we using the left connection received data on all people and their existing real estate. Remember the following with the left join sql example:

Suppose we want to refine the query and not output data, where the type of law is "Property". If we simply add, applying left join sql, an example the following condition:

...

We will lose data on people who do not have any real estate, because the empty value of Null does not compare this way:

To prevent errors from occurring for this reason, it is best to specify the selection condition immediately upon connection. We suggest to consider the following example with left join sql.

The result is as follows:

Thus, by performing a simple example with the left join sql, we got a list of all people, deducing additionally, which of them has real estate in shared / joint ownership.

As a conclusion I would like to emphasize once again that it is necessary to take responsibly to the selection of any information from the database. Many nuances have opened before us with the use of left join sql a simple example, the explanation of which is one - before we begin to compose even an elementary query, we must carefully understand what exactly we want to get as a result. Good luck!