SELECT ENAME, DNAME
FROM EMP, DEPT
ORDER BY ENAME;
카타시안 프로덕트 표기법 = AXB
SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT
ORDER BY ENAME;
[OS]
OSID (PK) | OS명 |
---|---|
100 | Android |
200 | iOS |
300 | Bada |
[단말기]
단말기ID (PK) | 단말기명 | OSID (FK) |
---|---|---|
1000 | A | 100 |
2000 | B | 200 |
3000 | C | 300 |
[고객]
고객번호 (PK) | 고객명 | 단말기ID (FK) |
---|---|---|
11000 | 홍길동 | 1000 |
12000 | 강감찬 | NULL |
13000 | 이순신 | NULL |
SELECT a.고객번호, a.고객명, b.단말기ID, c.OSID
FROM 고객 a LEFT OUTER JOIN 단말기 b
ON (a.고객번호 IN (11000, 12000) AND a.단말기ID = b.단말기ID)
LEFT OUTER JOIN OS c
ON (b.OSID = c.OSID)
ORDER BY a.고객번호;
[✔ SQL 쿼리 결과 테이블]
고객번호 | 고객명 | 단말기ID | OSID (FK) |
---|---|---|---|
11000 | 홍길동 | 1000 | 100 |
12000 | 강감찬 | NULL | NULL |
13000 | 이순신 | NULL | NULL |
SELECT A.ID, B.ID
FROM A FULL OUTER JOIN B ON A.ID = B.ID;
SELECT A.ID, B.ID
FROM A LEFT OUTER JOIN B ON A.ID = B.ID
UNION
SELECT A.ID, B.ID
FROM A RIGHT OUTER JOIN B ON A.ID = B.ID;
SELECT A.ID, B.ID
FROM A INNER JOIN B ON A.ID = B.ID
UNION ALL
SELECT A.ID, NULL
FROM A
WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.ID = B.ID)
UNION ALL
SELECT NULL, B.ID
FROM B
WHERE NOT EXISTS (SELECT 1 FROM A WHERE B.ID = A.ID);
[Oracle]
SELECT A.ID, B.ID
FROM A, B
WHERE A.ID = B.ID (+)
AND B.이름(+) = 'SONG'
AND A.이름 = 'HYUN';
[ANSI 표준]
SELECT A.ID, B.ID
FROM A LEFT OUTER JOIN B
ON (A.ID = B.ID AND B.이름 = 'SONG')
WHERE A.이름 = 'HYUN';