[SQL] DB/ANSI SQL, JOIN

1. ANSI SQL

SQL은 데이터베이스를 관리하기 위해 만들어진 언어로, DBMS마다 사용법이 조금씩 다르다. ANSI SQL은 미국 국립 표준 협회에서 제시한 표준 SQL로, Oracle, Mysql 등 대부분의 데이터베이스에서 사용할 수 있다. 

2. JOIN

(1) INNER JOIN

• 조인 조건에 해당하는 데이터만 SELECT한다.

-- ANSI
SELECT Customers.customer_id, first_name, item
FROM Customers
	INNER JOIN Orders
    	ON Customers.customer_id = Orders.customer_id;
        
-- ORACLE
SELECT Customers.customer_id, first_name, item
FROM Customers, Orders
WHERE Customers.customer_id = Orders.customer_id;

(2) OUTER JOIN

• 조인 조건에 일치하는 데이터와 일치하지 않는 데이터를 모두 SELECT한다.
• LEFT OUTER JOIN

-- ANSI
SELECT Customers.customer_id, first_name, item
FROM Customers
	LEFT OUTER JOIN Orders
    	ON Customers.customer_id = Orders.customer_id;
        
-- ORACLE
SELECT Customers.customer_id, first_name, item
FROM Customers, Orders
WHERE Customers.customer_id(+) = Orders.customer_id;

• RIGHT OUTER JOIN

-- ANSI
SELECT Customers.customer_id, first_name, item
FROM Customers
	RIGHT OUTER JOIN Orders
    	ON Customers.customer_id = Orders.customer_id;
        
-- ORACLE
SELECT Customers.customer_id, first_name, item
FROM Customers, Orders
WHERE Customers.customer_id = Orders.customer_id(+);

• FULL OUTER JOIN

-- ANSI
SELECT Customers.customer_id, first_name, item
FROM Customers
	FULL OUTER JOIN Orders
    	ON Customers.customer_id = Orders.customer_id;

(3) CROSS JOIN

• 모든 경우를 SELECT한다.

-- ANSI
SELECT Customers.customer_id, first_name, item
FROM Customers
	CROSS OUTER JOIN Orders;
        
-- ORACLE
SELECT Customers.customer_id, first_name, item
FROM Customers, Orders;