[Joins]SQL

[Two Tables]

Student

Subject Tables

Innter Join

교집합: INNER JOIN can use EXISTS

SELECT A.* from TA as A where EXISTS (Select * from TB b where C1 = A.C1)

Outer Join : Outer Join is  A-B(B- A∩B) == 

In Oracle use MINUS operator, others can use NOT EXIST or NOT IN

There are three type of joins, LEFT, RIGHT, FULL OUTER JOIN

In ANSI style can use *= (left), =* (right), full outer(for full)

ex) SELECT * FROM TBA a, TBB b WHERE a.id *= b.id -- SELECT * FROM TBA a left join TBB b on a.id = b.id

SELECT * FROM TBA a, TBB b WHERE a.id =* b.id-- SELECT * FROM TBA a right join TBB b on a.id = b.id

SELECT * FROM TBA a full outer join TBB b on a.id = b.id-- full outer join

Left Outer Join

 

Right Outer Join

 

Full Outer Join

 Cross 

SELF JOIN : It's not reference its table, but execute finaly query by selecting row sets from same table

ex) select a.EmployeeID, a.LastName, a.FirstName,
b.EmployeeID, b.LastName, b.FirstName
from Northwind..Employees as a,
(
  select EmployeeID, LastName, FirstName
  from Northwind..Employees
  where EmployeeID = 2
) as b
where a.ReportsTo *= b.EmployeeID

차집합 : 하나의 집합에서 다른 집합을 뺀 나머지를 찾는다. ANSI-SQL에서는 EXCEPT 키워들 사용한다. 오라클에서는 MINUS를 이용한다. A-(A ∩ B) =

can use NOT EXISTS to remove second set, NOT EXISTS is better than NOT IN because (A∩B) is less than B(A∩B < B).

합집합:UNION(remove duplicated), UNION ALL(not remove duplicated) == A+B ==  

ex) A={1,2,3}, B={3,4} A UNION B = {1,2,3,4}, A UNION ALL B = {1,2,3,3,4}