Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how to use the inner and outer connections of SQL". The editor shows you the operation process through an actual case. The method of operation is simple and fast, and it is practical. I hope this article "how to use the inner and outer connections of SQL" can help you solve the problem.

What is JOIN in SQL?

The JOIN operator allows you to combine relevant information in a variety of ways, as I explained briefly above. There are many types of connections, which are divided into two categories-internal connections and external connections.

The biggest difference between INNER JOIN and OUTER JOIN is that the inner join will retain only information from the two tables related to each other (in the result table). On the other hand, the outer join retains information that is independent of another table in the result table.

Let's take a closer look at how INNER JOIN and OUTER JOIN work to better understand them.

How to use INNER JOIN in SQL

The inner join retains only information from the two associated tables. If you think of these two tables as Venn diagrams, the tables generated by INNER JOIN will be the green highlights where they overlap:

Venn diagram representation of inner connection

This is the syntax for inner joins:

SELECT * FROM table1 JOIN table2 ON relation

Inner join syntax

We will use an example below to see how it works.

How to use OUTER JOIN in SQL

If you want to retain all data, not just interrelated data, you can use OUTER joins.

There are three types of outer joins: LEFT JOIN,RIGHT JOIN, and FULL JOIN. The difference between them relates to what irrelevant data they retain-it can come from the first table, the second table, or both. The value of a cell with no data to populate is NULL.

Note: LEFT JOIN is the most common implementation of all versions of SQL. But this is not the case for RIGHT JOIN and FULL JOIN, which was not implemented in earlier versions of SQL (but oracle databases are more fully implemented in terms of data table joins).

Let's see how everyone works individually. Then we will use the following example to see how they work.

Left outer connection in SQL

LEFT OUTER JOIN, or Left Join for short, retains irrelevant data from the left (first) table.

You can imagine it with a Venn diagram with two circles, and the resulting table is highlighted in green, including the common / overlapping part and the rest of the circle on the left.

Venn diagram representation of left outer connection

The syntax is as follows. You will see that it is similar to the Inner Join syntax, but with the addition of the LEFT keyword.

SELECT columns FROM table1 LEFT JOIN table2 ON relation

Left outer connection

Right outer join in SQL

RIGHT OUTER JOIN, or Right Join for short, retains data from the second table that has nothing to do with the first table.

You can imagine it with a Venn diagram with two circles, and the resulting table is highlighted in green, including the overlap and the rest of the circle on the right.

Venn diagram representation of right outer connection

The syntax is as follows, and the only difference is the RIGHT keyword.

SELECT columns FROM table1 RIGHT JOIN table2 ON relation

Right outer connection

Full outer join in SQL

You can think of FULL OUTER JOIN as a combination of left and right connections. It will retain all rows in both tables, and the missing data will be NULL.

You can imagine it with a Venn diagram with two circles, and the resulting table is highlighted in green, including everything: the overlap, the left circle, and the right circle.

Venn diagram representation of complete outer connection

The syntax is as follows, using the FULL keyword.

SELECT columns FROM table1 FULL JOIN table2 ON relation

Full external connection

Example of SQL JOIN operator

The possible database of veterinary clinics can have a table for pets and a table for owners. Because an owner may have multiple pets, the pets table will have an owner_id column pointing to the owner table.

ID card name age owner _ ID1 Fido 712 Miss 313 sissy 1024 Copper 135 hopper 20 ID card name and telephone number 1 Johnny 45678232 Ollie 74865133 Ilania 34813654 Louise 1685364

You can use a simple query to get a table with pet names and owner names adjacent to each other. Let's do this with all the different JOIN operators.

Example of SQL inner join

Let's first use JOIN.

In this case, you will SELECT to select the column name in the pets table (and rename it pet_name). Then select the column name from the owners table and rename it to owner. It will look like this: SELECT pets.name AS pet_name, owners.name AS owner.

Next, you can use the FROM keyword to declare that the column is from the pets table, and the JOIN keyword to declare that you want to join the owners table: FROM pets JOIN owner.

Finally, when the two columns that need to be added correspond one to one, use the ON keyword to connect the owner_id of the pets table with the id of the owners table using the equal sign: pets.owner_id = owners.id.

This is all:

SELECT pets.name AS pet_name, owners.name AS owner FROM pets JOIN owners ON pets.owner_id = owners.id

You will be given the following form, which includes only pets related to owners and owners related to pets.

Pet name owner Frito Johnny Miss Johnny sissy Ollie Copper Ilania SQL LEFT JOIN example

Let's execute the same query, LEFT JOIN so that you can see the differences. The query is the same except for adding the LEFT keyword.

SELECT pets.name AS pet_name, owners.name AS owner FROM pets LEFT JOIN owners ON pets.owner_id = owners.id

In this case, the row pets in the left table is preserved, and when data is missing in the owners table, the NULL is populated with.

Pet name owner Frito Johnny Miss Johnny sissy Ollie Bronze Ilania

It seems that one pet is not registered with its owner.

SQL right connection exampl

If you use using to execute the same query RIGHT JOIN, you will get different results.

SELECT pets.name AS pet_name, owners.name AS owner FROM pets RIGHT JOIN owners ON pets.owner_id = owners.id

In this case, all the row owners in the right table is reserved, and if there is a missing value, populate the NULL with.

Pet name owner Frito Johnny Miss Johnny sissy Ollie Copper Ilania empty Louise

There seems to be an unregistered pet owner.

SQL full connection exampl

You can execute the same query again, using FULL JOIN.

SELECT pets.name AS pet_name, owners.name AS owner FROM pets FULL JOIN owners ON pets.owner_id = owners.id

The resulting table is different again-in this case, all rows in both tables are retained.

Pet name owner Frito Johnny Miss Johnny sissy Ollie Copper Ilania hopper empty Louise

There seems to be no owner's pet and no pet owner in our database.

This is the end of the content about "how to use the inner and outer connections of SQL". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.