Summary of SQL Union table queries

1. Union

The UNION operator is used to combine the result set of two or more SELECT statements.

The UNION operator derives a result table by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table.

When all is used with the Union (that is , union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.

Note: When you use union, the results of the two table queries have the same number of columns, and the column types are similar.

Student table information (Students):

Id	Name	Age	City	Majorid
101	Tom	20	Beijing	10
102	Lucy	18	Shanghai	11

Teacher table Information (Teachers):

Id	Name
101	Mrs Lee
102	Lucy

Preset script:

INSERTInto Students (Id,name,age,city,majorid)VALUES (101,‘Tom‘,20,‘Beijing‘,10)INSERTInto Students (Id,name,age,city,majorid)VALUES (102,‘Lucy‘,INSERT into Teachers (id,name) VALUES (101,'Mrs Shanghai Lee')INSERT into Teachers (id,name) VALUES (102,'Lucy')  

1) Basic union query, query the school teacher, student's general information table, including ID and name

From StudentsUNION fromTeachers  

Query Result:

Id	Name
101	Mrs Lee
101	Tom
102	Lucy

2) with conditional union query, you can also query the same table, check the age of 18, 23 years old student information

where Age=theUNIONwhere age=     

Of course, this can be easily achieved using in or OR, here is just point to, later encountered complex query, I believe you will use.

3) Find all the names of teachers and students

Because union will only choose a different value, if the student has the same name as the teacher, this will require the UNION all

From Students all fromTeachers  

Query Result:

Id	Name
101	Tom
102	Lucy
101	Mrs Lee
102	Lucy

2. INNER join (inner join)

INNER join (inner join), also become a natural join

Effect: Queries data from these tables based on the relationship between the columns in two or more tables.

Note: An inner join removes all rows from the result that have no matching rows in the joined table, so the inner join may lose information.

Focus: Inner joins, only matching rows are checked.

Syntax: (inner can be omitted)

SELECT fieldlist[INNERjoin table2 ontable1.  Column=table2.  Column        

Student table information (Students):

Id	Name	Age	City	Majorid
101	Tom	20	Beijing	10
102	Lucy	18	Shanghai	11

Professional Information Sheet (majors):

Id	Name
10	中文版
12	Computer

Preset Script:

DELETEFromStudentsINSERTInto Students (Id,name,age,city,majorid)VALUES (101,‘Tom‘,20,‘Beijing‘,10)INSERTInto Students (Id,name,age,city,majorid)VALUES (102,‘Lucy‘,18,‘shanghai ") delete from Majorsinsert into majors (id,name) values (10, " english ) insert into majors (id,name) values (12, ' computer ")  

Example: Query student information, including ID, name, professional name

SELECT students.id,students.name,majors.name as MajornameJOIN Majors= majors.id   

Query Result:

Id	Name	Majorname
101	Tom	中文版

According to the results, it is clear that only matching rows are true. The student Lucy's information was lost.

3. Outer JOIN

The complete collection of a table is returned, even if there are no matching rows, compared to an inner join.

Outer joins are divided into three kinds: left outer join, right outer join, whole outer join. Corresponds to Sql:left/right/full OUTER JOIN. Usually we omit the outer keyword. Written as: Left/right/full JOIN.

Emphasis: At least one party retains the complete collection, and no matching rows are replaced with null.

1) left OUTER join, referred to as Ieft join, outer join (left join)

The result set retains all the rows of the left table, but contains only the rows that match the second table with the first table. The corresponding empty row of the second table is put into a null value.

Examples of links that are still in use

(1) Use the left link to query the student's information, including student ID, student name and professional name.

As majornameJOIN Majors= majors.id   

Results:

Id	Name	Majorname
101	Tom	中文版
102	Lucy	Null

Conclusion:

As a result, we can see that the left join contains all the information for the first table, and if there is no match in the second table, it is replaced with null.

2) Right outer join (right outer join)

A right outer join retains all the rows of the second table, but only the rows that match the second table with the first table. The first table is given a null value for the corresponding empty row.

The right join is similar to the idea of a left join. It's just the second one. If there is no match in the first table, use NULL instead

Still follow the example of internal links, but instead of right join

(1) Use right join to query student's information, including student ID, student name and professional name.

As majornameJOIN Majors= majors.id   

Query Result:

Id	Name	Majorname
101	Tom	中文版
Null	Null	Computer

As you can see from the results, the complete collection of the second table majors is included, and computer does not have a match in the students table, it is replaced with null.

3) Full join (fully OUTER join, all outer join)

Full OUTER JOIN, abbreviation: Full join. Displays all rows from two tables in the results table

1) Use the full join to query the student's information, including student ID, student name and professional name.

As majornameJOIN Majors= majors.id   

Query Result:

Id	Name	Majorname
101	Tom	中文版
102	Lucy	Null
Null	Null	Computer

Contains all the records for both tables, no records are lost, and no matching rows are replaced with null.

4. Cross join (crossover join)

Cross joins. A cross join returns all the rows in the left table, with each row in the left table combined with all the rows in the right table. Cross joins are also called Cartesian product.

Simple query Two table combination, this is to seek Cartesian product, the lowest efficiency.

Cartesian product: Cartesian product, also known as 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)}. Can be extended to multiple collections of cases. Similarly, if a represents a collection of students in a school and b represents a collection of all courses in the school, the Cartesian product of A and B represents all possible elective courses.

1) Cross join to query the student's information, including student ID, student name and professional name.

As majornameJOIN majors 

Query Result:

id	Name	majorname
101	Tom	中文版
102	Lucy	中文版
101	Tom	computer< /p>
102	Lu Cy	computer

2) query multiple tables, in fact, is a Cartesian product, and cross join equivalent, the following query is the same as the above results.

This may be common, but it is important to note that you are querying the complete set of all the combinations in both tables.

As majorname fromstudents,majors 

3) query conditions are added

Note: When you use the Cross join keyword to intersect a table, because the Cartesian product of two tables is generated, you cannot use the ON keyword, and you can define search conditions only in the WHERE clause.

As majornameJOIN Majors= majors.id   

Query Result:

Id	Name	Majorname
101	Tom	中文版

The query results are the same as inner joins, but their efficiency is much slower.

Summary of SQL Union table queries