Skip to main content

UnionJoin

Union語法​

  • 將不同欄位作崇值合併,不會增加欄位,但會增加資料筆數
  • 欄位數要一樣
  • 資料類型要一樣
  • 合併後會以第一個Select的欄位名稱為主
  • Group By和Having子句可用於Select中,但不可以用於Union之後的結果
  • Order By只能用於Union之後的結果,不可用於Select中
  • Union後若有重複只會出現一次,除非加了all參數
SELECT column_names FROM table1
UNION ALL
SELECT column_names FROM table2;

SELECT author as guest FROM book
Union 
SELECT contact as guest FROM publisher
ORDER BY guest;

mysql-union

Join語法​

  • 利用ON設定關聯件,將數個表格連接起來
  • 將不同欄位做水平合併,會增加欄位數
  • Inner Join：交集
  • Left Join：只有左邊表格有的值,右邊表格無此值會顯示NULL
  • Right Join：只有右邊表格有的值,左邊表格無此值會顯示NULL mysql-join

Self Join語法​

  • 一個欄位參照到同表格的另一個欄位
INSERT INTO employee(empID, name, managerID) VALUES(6,'Rani',NULL)
INSERT INTO employee(empID, name, managerID) VALUES(5,'Ram',6)
INSERT INTO employee(empID, name, managerID) VALUES(7,'Veeru',6)
INSERT INTO employee(empID, name, managerID) VALUES(4,'Kunal',5)
INSERT INTO employee(empID, name, managerID) VALUES(3,'Sonam',4)
INSERT INTO employee(empID, name, managerID) VALUES(1,'Rahul',3)
INSERT INTO employee(empID, name, managerID) VALUES(2,'Jay',3)
SELECT e.empID, e.name, e.managerID FROM employee e
LEFT JOIN employee m on e.managerID = m.employeeID;

mysql-selfjoin