ALBERT1017 JOIN statements in SQL statements

To understand the join statement, first understand the Cartesian product (the multiplication of the set), which is defined as follows:

The product of Descartes (Descartes) is also called direct product. Assuming collection a={a,b}, set b={0,1,2}, the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}.

In the join operation of SQL, in fact, it is the Cartesian product of the table first, and then select the result set, select the part that satisfies the condition as the result, it should be noted that how to treat the table as a

Set, the table is a two-dimensional data, we have a row of data in the table (that is, a record) as an element in the table collection, and then the Cartesian product operation.

Examples are as follows (for example from W3school):

Please see the "Persons" table:

City

id_p	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Note that the "id_p" column is the primary key in the Persons table. This means that no two lines can have the same id_p. Even if the names of two people are exactly the same, id_p can distinguish them from each other.

Next, look at the Orders table:

Id_o	OrderNo	id_p
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

Note that the "id_o" column is the primary key in the Orders table, and the "id_p" column in the Orders table is used to refer to people in the "Persons" table without using their exact names.

Please note that the "id_p" column links the above two tables.

Referencing two tables

We can get the data from two tables by referencing two tables:

Who ordered the products and what products they ordered?

Result set:

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

SQL join-Using Join

In addition to the above method, we can also use keyword JOIN to get data from two tables.

If we want to list everyone's orders, you can use the following SELECT statement:

SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom Persons on INNER JOIN Orders persons.id_p = Orders.id_porder by Persons.lastname

Result set:

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

Different SQL JOIN

In addition to the INNER join (inner join) we used in the example above, we can also use several other connections.

The following lists the JOIN types that you can use, and the differences between them.

• Join (INNER JOIN): Returns a row if there is at least one match in the table (Note: INNER join is the same as join.) ）
• Left JOIN (outer join:): Returns all rows from the left table, even if there is no match in the right table (note: In some databases, the Ieft join is called the OUTER join. ）
• Right join: Returns all rows from the right table, even if there are no matches in the left table (note: In some databases, the correct join is called a OUTER join. ）
• Full join (all outer joins): Returns a row whenever a match exists in one of the tables (note: In some databases, the complete join is called the full OUTER join. ）

Explanation: The result set of the junction operation is also in the behavior element, here the match, refers to the condition after the on is satisfied, satisfies the match.

Also: Crossjoin: the Cartesian product (all possible pairs of rows); The join has no subsequent on condition, and returns the entire result of the product of the set (note: There is an on condition in addition to the Cross join).

If you do not understand it, take the collection to further illustrate:

Assuming collection a={a,b}, set b={0,1,2}, the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}. Note that the element a,b;0,1,2 in the collection represents the data for one row in the table, (a,0) represents a record of the result, and if it is an inner join, to determine whether the columns in each result record satisfy the conditions on the back, or to be discarded as a record of the final result. The outer join takes the left external junction as an example, traversing the left set a (table) each element, each of which iterates through each element of the right set B (table), there is a match as the return result, if there is no match after traversing the B each element, then take the element of table A, which would have taken the part of B completely empty, A record that consists of the returned result.

For the LEFT join do the following examples:

You can use the following SELECT statement:

SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsleft JOIN Orderson persons.id_p=orders.id_ Porder by Persons.lastname

Result set:

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George

The left JOIN keyword returns all rows from the table (Persons), even if there are no matching rows in the right table (Orders).

Note that the yellow section differs from the first example. The right join is identical to the left join. Give an example of a full (outer) join:

You can use the following SELECT statement:

SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsfull JOIN Orderson persons.id_p=orders.id_ Porder by Persons.lastname

Result set:

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George
		34764

The full JOIN keyword returns all rows from the left table (Persons) and the right table (Orders). If the rows in "Persons" do not match in the table "orders", or if the rows in "orders" do not have a match in the table "Persons", the rows are also listed.

Note that the red part differs from the previous two examples.

ALBERT1017 JOIN statements in SQL statements