SQL join, union, union all， 完整的select语句

https://www.cnblogs.com/lijingran/p/9001302.html

                               where        on

笛                          ok               no

corss join            ok               ok     执行计划一样

inner join             ok                ok    执行计划一样

left     join            no                 ok

right  join             no                 ok

full     join             no                 ok

 

1.笛卡尔积

select A.clue_id as A_clue_id,  A.cooperate_days A_days, B.clue_id as B_clue_id, B.cooperate_days B_days from

clue_pri_sea A, clue_source B where A.clue_id in (3328, 3584) and A.clue_id=B.clue_id;

 

2.左连接  left  join 

 

select * from clue_pri_sea  left join clue_source on clue_pri_sea.clue_id=clue_source.clue_id  where clue_pri_sea.clue_id=1\G

 

 

3.右连接  right  join

select * from clue_pri_sea  right join clue_source on clue_pri_sea.clue_id=clue_source.clue_id  where clue_pri_sea.clue_id=1\G

 

 

4. union  和 union all

 

union操作符用于合并两个或者多个select语句的结果集。

要求：union的两边的表，必须拥有相同数量的列，顺序也要一样，列数据类型也要一样。

另外，结果集的列名和第一个select语句列名是一样的。

union命令会将相同的行合并成一个； union all 不做合并。

 

 

 

 

5.完整的select语句

 

select [ALL | distinct] field1, field2 as alias from xxxTable where   group by  having order by

 

 

关联子查询

SELECT * from film WHERE film_id in (SELECT film_id FROM film_actor WHERE actor_id=1);

 

 

MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.