Join and Union differences and usage in SQL

Reprint: http://chengheng1984.blog.163.com/blog/static/17947412201012215738844/

Join is used to join two tables on an on condition, with four main types:

INNER join: Internally joins records from two tables, and the inner join returns rows only if there is at least one row that belongs to two tables that matches the join condition. I understand that as long as the record does not meet the on condition, it is not displayed in the result set.

Left Join/left OUTER join: Outer joins records from two tables and contains all records from the table on the right. If a record in the left table does not have a matching record in the right table, all select list columns in the right table in the associated result set are null values. Understand that even if the on condition is not met, the records in the left table are all displayed, and the right table field of the record in the result set is a null value.

Right join/right OUTER join: Outer joins records from two tables, and contains all records in the table on top. In short, it is the opposite of the left join.

Full Join/full OUTER join: The complete outer join returns all rows from the left and right tables. It is the left join and right join and merge, and the data of both tables is displayed.

Basic syntax for join:

Select table1.* from table1 JOIN table2 on Table1.id=table2.id

Union operator

Combines the result set of two or more queries into a single result set that contains all the rows of all the queries in the Union query. The result set column name of the union is the same as the column name of the result set of the first SELECT statement in the Union operator. The result set column name of another SELECT statement is ignored.

Two of the different usages are union and union all, except that the union removes duplicate rows from the result set. If you use union ALL, all rows will be included and duplicate rows will not be deleted.

(ZZ)

1. Intersection

Inner Join, equivalent to select * from a b where a.id=b.id, * denotes a.*,b.*, a record you can remove the field

A LEFT JOIN or right join can represent a difference set, and a record you can remove the field of a A, a, b

Full join is the representation of the set, select * from a full join B on a.id=b.id

, A.id=b.id is a record, other unequal records can be displayed, such as the field of a and the null field combination, or the null field and a combination ~

Another cross join, if not added to the where condition, is the record of a and a record of B any combination, add a has 15, B has 15, then a cross join B has 225 strokes, there will be no null field (of course, the original table has a different)

Cross join plus where is inner join

SELECT * from a crossjoin b where a.id = b.id equivalent to select * from a full join B on a.id=b.id

P.S. Not In-->select * from a where a.id in (select b.ID from B), * indicates that a you can only remove the field of a A, not exists, and this is not actually the scope of the discussion of intersection or difference set, each Times just to select a table's record, see this sentence select * from a where a.id not in (' 1 ', ' 2 ') This calculation is not a difference set

2.UNION represents a set, and I don't think that's the way to say it all.

Union is not the same as join, union this operator is merging columns, and join is merging the fields (as I said earlier)!

If you merge from field, full join is a union, inner join is the intersection! The LEFT JOIN or right join is not entirely a difference, it also includes the result of the intersection, how the query result of your statement or the actual statement, such as Cross join, plus where it becomes inner join, the results are very far apart

I looked it up online and found something new:

Unions: Union:selectxfrom a Union (all) Selectxfrom b aUb

Intersection: Intersect:selectxfrom A intersect selectxfrom b ANB

Differential set: Minus:selectxfrom a minus selectxfrom B a

Join and Union differences and usage in SQL