Summary of four situations of joint query between two tables in Mysql

Generally speaking, in order to get more complete results, we need to get results from two or more tables. I usually use select xxx,xxx from table1, table2 where table1.xxx=table2.xxx. We usually perform such operations. In fact, there is another operation in MySQL, that is, join operation. For example, there are two tables below:

Take a look at the "Persons" table:

Next, look at the "Orders" table:

The above two tables, then we connect them to query

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P

Result Set:

If you use the keyword JOIN to get data from two tables

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

Result Set:

Then the inner join is no different from the normal query above

Different SQL JOINs

• JOIN: Returns rows if there is at least one match in the table
• LEFT JOIN: Returns all rows from the left table even if there are no matches in the right table
• RIGHT JOIN: Returns all rows from the right table even if there is no match in the left table
• FULL JOIN: Returns rows if there is a match in either table.

LEFT JOIN Keyword Syntax

"Persons" table:

"Orders" table:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

Result Set:

SQL RIGHT JOIN Keyword

"Persons" table:

"Orders" table:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

Result Set:

SQL FULL JOIN keyword

"Persons" table:

"Orders" table:

FULL JOIN Example

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

Result Set:

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links