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

Article's Author Tables

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.

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;

			
	

			
	
			

		
	

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;

			
	

			
	
			

		
	

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.