A summary of several common uses of JOIN in SQL

The join joins combine the field records in two tables, including three types:

inner join operation: concatenate and combine field records in two tables.

Left Join Operation: Concatenate and combine the field records in two tables, and will contain all the records in the left join table.

Right Join Operation: Concatenate and combine field records in two tables, and will contain all the records in the right join table.

inner join sets the operation formula for linking two tables to join and combine field records in two tables.

The inner join syntax is as follows:

from Table 1 inner join Table 2 on Table 1Field 1 Comparison Operator Subtable 2Field 2

A field joined by two tables, e.g. [Table 1.]Field 1 = Table 2Field 2], it must have the same field type, but the field name does not need to be the same.

For example, an autonumbering field type can join a long field type, but a single fine integer field type cannot join a double fine integer field type.

The comparison operator can be =, <, =, >=, or <>.

The join field cannot contain the memo field type or the ole object type, otherwise an error will occur.

In a join expression, multiple on clauses can be concatenated:

select fields

from Table 1 inner join Table 2

on Table 1Field 1 Comparison Operator Subtable 2Field 1 and

on Table 1Field 2 Comparison Operator Subtable 2Field 2) or

on Table 1Field 3 Comparison Operator Subtable 2Field 3).

join expression, it can be a nest:

select fields

from Table 1 inner join

Table 2 inner join [(Table 3.)

inner join [(Table x [inner join...)

on Table 3Field 3 Comparison Operator Subtable xfield x)].

on Table 2Field 2 Comparison Operator Sub-Table 3Field 3).

on Table 1Field 1 Comparison Operator Subtable 2Field 2

In an inner join, you can include a nested left join or right join, but you cannot include a nested inner join in a left join or right join.

Left Jion and Iner Jion in Oracle are two different association types of Jion. The left jion is the left outer join, the data associated with the two tables is mainly the data from the right table, and the inner jion is the inner join. Here's how it works;

1. The same point can be added to the end of the where condition, except for cross join, other connections must be added to the ON key. Natural joining is to find fields with the same data type and column name in two tables, and then automatically join them and return all the results that match the criteria.

2. The following figure shows the relationship between the two tables, there are two field data types, which are the same as the column names, so when the natural connection query is performed, the results that meet the conditions of both fields will be queried.

3. You can use the using clause to specify the qualifications for two tables, but when using using, the previous natural join only needs to be written as join.

4. You can also use on to specify constraints for two tables, but when using on, natural join can only use join, and the query result is 106.

5. You can also use the where condition to connect the two tables for query, and connect the relationship between the two tables by associating fields.

In layman's terms, an analogy.

select * from a inner join b on = , the query result displayed is the data that the query condition a must be equal to b;

select * from a left join b on = , the query result displayed is all the data in the left table and the data in the right table that meet the query conditions.

There is also a right jion, select * from a right join b on = , right join, which shows the query results of all the tables on the right, and the data that matches the query conditions of the left table.

Left Jion and Iner Jion are two different association types of Jion;

left jion is a left-outer join, the two tables are related, and the data that comes out is mainly the data from the right table. In the same way, the right jion cut is the opposite of the left outer connection rule;

inner jion is an inner join, the key fields of the two tables are joined by straight and equal, and the data associated with the two tables exists in the same table.

SQL....Where can be used with inner join, and the solution is as follows:

1. First, create a temporary table for inner join in sqlserver syntax.

2. Create another temporary table to demonstrate the use of inner join in sqlserver syntax.

3. Insert a few rows of test data into the temporary table, including inserting null values.

4. Query the test data in the temporary table select * from tmp1; select * from #tmp2;。

Yes, the specific syntax is as follows.

select [student number] [class].

from [Student Table] as a

inner join [class table] as bon a[class id] = b[class ID]where a[Student ID] = 200401

select inbound table. Serial number, drug name list. Product name, warehousing table. Incoming price, inbound table. Quantity (kg), warehousing table. Incoming price, inbound table. Quantity as Amount, Inbound Table. Inbound date.

from Inner join drug name list on Warehousing. Serial number, drug name list. Serial number.

where Inbound table. Serial number = order by inbound table. Inbound date desc

JOIN includes: inner join, outer join

Outer join includes: left outer join, right outer join, and full outer join

natural on using

1) The ON condition is the condition used when generating a temporary table, and it will return the records in the left table regardless of whether the condition in ON is true or not. Lu Tsai.

2) The where condition is the condition for filtering the temporary table after the temporary table is generated. At this time, there is no meaning of left join (the record of the left table must be returned), and if the conditions are not true, Chang Tsai will filter them out.

For such a query select * from t1 natural left outer join t2 on

ON condition returns all the elements on the left, and the result should contain the tuple (3,30,null).

For another query, select * from t1 natural left outer join t2 where

The result does not contain the tuple (3,30,null), because when where is executed, the filter condition will be executed after the intermediate table (i.e., Table 1) is generated, and it is obvious that t2...size=null, so the result after execution is as follows:

If you compare Table 1 and Table 2, you will find that Table 1 has one more column than Table 2, which is actually a feature of ON.

In the book, it is written, "In the ON operation, size appears twice, once in t1 and once in t2, even though their values are the same." ”

The above example confirms one point: all the external connection conditions must be placed behind on, and all the left and right associations in front of Wang Ran will be used as ornaments and have no effect. And for normal connections, there is almost no difference.

noun n

1.junctions; Contiguous [c].

the joins can hardly

To put it simply, join is a transitive verb, directly add an object, the object is an organization, etc., and join in is an intransitive verb, which is equivalent to take part in followed by an activity, etc.

Transitive verb vt

1.Link; Make the binding [(+to together up)].

he joined the two pieces of wood together with glue.

He glued the two pieces of wood together.

2.And. Rendezvous; And. Connect.

this path joins the highway up ahead.

The road meets the road in front.

3.And. Do the same thing together; And. Companion [(+in for)].

will you join us for dinner?

Would you like to have dinner with us?

4.Attend; Make. members.

i'll persuade him to join our club.

I will persuade him to join our club.

5.Adjacency. the two houses join each other.

The two houses are adjacently adjacent.

6.Back to (post, etc.).

after a few days on shore the sailor joined his ship.

After a few days on shore, the sailor returned to his ship.

Intransitive verb vi

1.Rendezvous; Meet.

where do the two rivers join?

Where do these two rivers meet?

2.Attend; Same. Together [(+in)].

he never joined in the usual sports of the boys.

He never participates in the children's general sports activities.

3.Adjacency. the two estates join at the foot of the hill.

The two properties are adjacent to each other at the foot of the hill.

JOIN is generally used as JOIN IN, which refers to public activities or meetings, etc., and often refers to the participants who have a positive attitude and play a role. Like take part in, join in usually refers to participating in a certain activity, especially to participate in a certain activity with other people, and the two can be interchanged.

Join is used as a noun yes meaning: join, connect, connect. Examples:

there' s a join in this piece of material.The meanings of the verb used for join are: 1

Connect, through 2Rendezvous, Huikai ruler, intersection 3Join, participate, participate, join means to participate, you can use join in, which refers to joining an organization and becoming a member of it, generally refers to participating in an event, but it is not very large.

The phrases that also indicate participation and joining are: participate in, be involved in, take part in, and the words for participating include attende, present, etc. These phrases and words all have the meaning of participation, but they also have different meanings and usages, and are also used in different occasions.