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

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 Table 1, Table 2 where Table 1.xxxx = Table 2.xxx. We usually do this kind of operation. In fact, there is another operation in mysql, that is, join operation, for example, there are two tables below:

Please look at the "Persons" table:

Next, look at the "Orders" table:

The above two tables, and then we make a join query on them

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons, OrdersWHERE 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.OrderNoFROM PersonsINNER JOIN OrdersON Persons.Id_P = Orders.Id_PORDER BY Persons.LastName

Result set:

Then inner join is no different from the ordinary query above

Different SQL JOIN

JOIN: if there is at least one match in the table, return row LEFT JOIN: return all rows RIGHT JOIN from the left table even if there is no match in the right table; FULL JOIN all rows from the right table even if there is no match in the left table: return rows as long as there is a match in one of the tables

LEFT JOIN keyword syntax

"Persons" table:

"Orders" table:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsLEFT JOIN OrdersON Persons.Id_P=Orders.Id_PORDER BY Persons.LastName

Result set:

SQL RIGHT JOIN keyword

"Persons" table:

"Orders" table:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsRIGHT JOIN OrdersON Persons.Id_P=Orders.Id_PORDER BY Persons.LastName

Result set:

SQL FULL JOIN keyword

"Persons" table:

"Orders" table:

Fully connected (FULL JOIN) instance

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsFULL JOIN OrdersON Persons.Id_P=Orders.Id_PORDER BY Persons.LastName

Result set:

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.