SQL的各種連接

1 create table students (c_id int, c_name varchar(20));
2 create table subjects (c_id int, c_subject varchar(20));
3 
4 insert into students values (1,'張三'),(2,'李四'),(3,'王五');  -- orcle不支持這種批量插入的寫法
5 insert into subjects values (1,'語文'),(2,'數學'),(null,'英語');

1 select * from students s join subjects t on s.c_id = t.c_id;

1 select * from students s left join subjects t on s.c_id = t.c_id;

1 select * from students s right join subjects t on s.c_id = t.c_id;

1 -- oracle
2 select * from students s full join subjects t on s.c_id = t.c_id; 

1 -- mysql，因myql不支持完全連接，直接使用full join會報語法錯誤，可以通過union將左鏈接查詢和右鏈接查詢的結果合并起來從而得到全連接的效果
2 select * from students s left join subjects t on s.c_id = t.c_id
3 union
4 select * from students s right join subjects t on s.c_id = t.c_id;

1 select * from students s cross join subjects t;