SQL JOIN and sqljoin

SQL JOIN and sqljoin

When the charging system of the data room is restructured, many functions need to query matching information in another table based on the information in one table in the database. We use the view, but have you noticed the following SQL statements? (SELECT... FROM... JOIN... ON...), let's take a look at JOIN in SQL.

1. What is JOIN?

Join is one of the important operations of the relational database system. Common Join Operations in SQL Server include: inner Join, outer Join, and cross Join, join is used to query data from two or more tables based on the relationship between columns.

 

 

2. How to Use JOIN?

Create two tables as an example. The table is as follows:

● INNER JOIN

SQL statement

<span style="color:#333333;">select * from T_NumA </span><span style="color:#ff0000;">inner join</span><span style="color:#333333;"> T_NumB </span><span style="color:#ff0000;">on</span><span style="color:#333333;"> T_NumA .aID =T_NumB .bID </span>

The result is as follows:

The result is the intersection of two tables. The figure on the right shows that Inner join conforms to the exchange law: "A inner join B", and "B inner join.

● Outer join 1), LEFT (OUTER) JOIN and RIGHT (OUTER) JOIN

SQL statement

<span style="color:#333333;">select * from T_NumA </span><span style="color:#ff0000;">left outer join</span><span style="color:#333333;"> T_NumB on T_NumA .aID =T_NumB .bID </span>

<span style="color: rgb(51, 51, 51); ">select * from T_NumA </span><span style="color:#ff0000;">right outer join</span><span style="color:#333333;"> T_NumB on T_NumA .aID =T_NumB .bID </span>

Running result

LEFT:

Running result: the complete set of Table A is generated, while that of Table B is matched with A value. Otherwise, null is replaced.

RIGHT:

From the running results, we can see that the join generates A complete set of Table B, while Table A matches A value, and if no match exists, it is replaced by A null value.

2), FULL (OUTER) JOIN

SQL statement

<span style="color:#555555;">select * from T_NumA </span><span style="color:#ff0000;">full outer join</span><span style="color:#555555;"> T_NumB on T_NumA .aID =T_NumB .bID </span>

Running result:

 

The result shows that the join generates the Union of A and B. However, it should be noted that for records without matching, null is used as the value.

● CROSS JOIN

SQL statement

<span style="color:#555555;">select * from T_NumA </span><span style="color:#ff0000;">cross join</span><span style="color:#555555;"> T_NumB </span>

Running result:

 

From the running results, we can see that the result of the Cross-join operation is the Cartesian Product we learned in the database system principle to generate a result set of 6*6 = 36 rows of records.

3. Conclusion

The join statements mentioned in this article are to vertically concatenate columns in different tables, because during the individual restructuring of the data center billing system, only query of vertically spliced tables is involved, there must also be a number of horizontal splicing methods: Not in, Union, and Intersect. Here we will just give a brief introduction. Not in is the difference set of two result sets. Union is the Union of two sets. duplicate records are automatically deleted, and All record rows can be retained with Union All. Intersect is the intersection of two sets, that is, records that exist in both sets.

If anything is wrong, you are welcome to make an axe !!!

The use of join on in SQL

SELECT customer number, customer ordering table. Dish number, Dish name, unit price, quantity;
From join menu table;
ON customer ordering table. Dish No. = menu table. Dish No;
WHERE unit price> = 40;
Order by order table. Dish No. DESC;

Can be replaced:

SELECT customer number, customer ordering table. Dish number, Dish name, unit price, quantity
FROM customer ordering table, menu table
WHERE
Customer ordering table. Dish No. = menu table. Dish No.
AND unit price> = 40
Order by customer ordering table. Dish No. DESC

For JOIN examples, refer:
INNER/LEFT/RIGHT/CROSS/FULL JOIN
Hi.baidu.com/..2.html

The use of join on in SQL

SELECT customer number, customer ordering table. Dish number, Dish name, unit price, quantity;
From join menu table;
ON customer ordering table. Dish No. = menu table. Dish No;
WHERE unit price> = 40;
Order by order table. Dish No. DESC;

Can be replaced:

SELECT customer number, customer ordering table. Dish number, Dish name, unit price, quantity
FROM customer ordering table, menu table
WHERE
Customer ordering table. Dish No. = menu table. Dish No.
AND unit price> = 40
Order by customer ordering table. Dish No. DESC

For JOIN examples, refer:
INNER/LEFT/RIGHT/CROSS/FULL JOIN
Hi.baidu.com/..2.html