sql join
When retrieving data from the DB, I think you have selected “Retrieving AA
from BB
“.
And when retrieving this data, I think there are many cases where data is searched and acquired from multiple tables.
However, for example, it takes time to do “Search A ⇒ Check B ⇒ Check two search results” for tables A and B, right?
Therefore, in the case of a normal search, “collecting the tables of A and B ⇒ search from there” is used.
This ” combining ” is called ” joining “, and there are methods such as inner joining and outer joining that will be dealt with from now on.
join sql
Post Tables
id | name | author_id |
---|---|---|
1 | Post 1 | 1 |
2 | Post 2 | 2 |
3 | Post 3 | 1 |
Four | Post 4 | Three |
Article’s Author Tables
id | name |
---|---|
1 | Adam |
2 | Alice |
3 | Matt |
Inner join and Outer join
First, there are two main methods for joining two tables
- inner join
- outer join.
Inner join
Inner joins join only those that match the values of the specified columns in each table.
Let’s first look at the basic syntax.
SELECT col_name_1, col_name_2 FROM table name 1 INNER JOIN table name 2 ON join condition
With Our Table
SELECT * FROM articles INNER JOIN authors ON articles.author_id = authors.id;
Use the statement inner join for inner join instructions . As a result of executing this SQL statement, such a table is created.
id | name | author_id | id | name |
---|---|---|---|---|
1 | Post 1 | 1 | 1 | Adam |
3 | Post 3 | 1 | 1 | Adam |
2 | Post 2 | 2 | 2 | Alice |
Notice that the Post 4 in the posts table is gone.
The author_id of Matt is 3. However, id does not exist in the authors table.
In the case of an inner join, any records that do not match the conditions are deleted from the base table .
Outer join
Outer joins , such as inner joins, join those with matching column values in each table, as well as those that exist in only one of the tables.
SELECT col_name_1, col_name_2 FROM table 1 LEFT (RIGHT) OUTER JOIN table name 2 ON join condition
A new word called LEFT (RIGHT) OUTER JOIN has come out.
Both LEFT OUTER JOIN and RIGHT OUTER JOIN define the method of outer join.
- LEFT OUTER JOIN : How to perform an outer join around the table on the left as the axis
- RIGHT OUTER JOIN : How to perform an outer join with the table on the right as the axis
Outer join in LEFT OUTER JOIN
SELECT * FROM articles LEFT OUTER JOIN authors ON articles.author_id = authors.id;
id | name | author_id | id | name |
---|---|---|---|---|
1 | Post 1 | 1 | 1 | Adam |
3 | Post 3 | 1 | 1 | Adam |
2 | Post 2 | 2 | 2 | Alice |
Four | Post 4 | 3 | null | null |
You can see that Post 4, which was deleted from the table because there was no matching data at the time of inner join, remains as data in the case of outer join.
Outer join in RIGHT OUTER JOIN
SELECT * FROM articles RIGHT OUTER JOIN authors ON articles.author_id = authors.id;
id | name | author_id | id | name |
---|---|---|---|---|
1 | Post 1 | 1 | 1 | Adam |
3 | Post 3 | 1 | 1 | Adam |
2 | Post 2 | 2 | 2 | Alice |
null | null | null | 3 | Matt |
Since the authors table on the right side is the standard, all the items in the authors table are retrieved.
Then, as a result of searching the posts table for the corresponding data, it became like this. Also note that the sort order is id order in the articles table.