SQL UNION子句/操作符用于合并兩個(gè)或多個(gè)SELECT語句的結(jié)果,不返回任何重復(fù)的行。
要使用UNION,每個(gè)SELECT必須選擇相同的列數(shù),相同數(shù)目的列表達(dá)式,相同的數(shù)據(jù)類型,并讓它們以相同的順序,但它們不必具有相同的長度。
UNION的基本語法如下:
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
在這里,給定的條件可能是根據(jù)您的需要給定任意的表達(dá)。
考慮下面的兩個(gè)表中,(a)CUSTOMERS表如下:
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
(b)另一個(gè)ORDERS表如下:
+-----+---------------------+-------------+--------+ |OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 | 3 | 3000 | | 100 | 2009-10-08 00:00:00 | 3 | 1500 | | 101 | 2009-11-20 00:00:00 | 2 | 1560 | | 103 | 2008-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+
現(xiàn)在,讓我們在SELECT語句中加入這兩個(gè)表如下:
SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID UNION SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
這將產(chǎn)生以下結(jié)果:
+------+----------+--------+---------------------+ | ID | NAME | AMOUNT | DATE | +------+----------+--------+---------------------+ | 1 | Ramesh | NULL | NULL | | 2 | Khilan | 1560 | 2009-11-20 00:00:00 | | 3 | kaushik 上一篇:SQL TRUNCATE TABLE(截?cái)啾?下一篇:SQL LOCALTIME和LOCALTIME()函數(shù)