T-SQL不同的條件篩選方式 IN、BETWEEN、LIKE、ALL、ANY、EXISTS

 

先準備範例資料表

CREATE TABLE EMPLOYEE 
  ( 
     no         TINYINT, 
     name       NCHAR(10), 
     profession NVARCHAR(10), 
     sex        NVARCHAR(5), 
     birthday   DATE, 
     PRIMARY KEY (no) 
  ) 

go 

INSERT dbo.EMPLOYEE VALUES (1, N'Brooke    ', N'ceo', N'boy', '19840404')
INSERT dbo.EMPLOYEE VALUES (2, N'Mary      ', N'eng', N'girl', '19990101')
INSERT dbo.EMPLOYEE VALUES (3, N'Bill      ', N'eng', N'boy', '19200120')
INSERT dbo.EMPLOYEE VALUES (4, N'john      ', N'sale', N'boy', '19901112')
INSERT dbo.EMPLOYEE VALUES (5, N'tom       ', N'sale', N'boy', '19990101')

 

一、利用IN篩選資料

語法：

[ NOT ] IN ( 單值或多值運算式)

如下範例表示當條件符合於('ceo','eng')集合內則秀出資料來

SELECT no,name,profession,sex,birthday  FROM EMPLOYEE
WHERE profession IN ('ceo','eng')

 

二、利用BETWEEN ...AND...篩選資料

語法：

[ NOT ] BETWEEN 起始運算式 AND 終止運算式

SELECT no,name,profession,sex,birthday  FROM EMPLOYEE
WHERE birthday BETWEEN '1990-01-01' AND '1999-01-01'

等於

SELECT no,name,profession,sex,birthday  FROM EMPLOYEE
WHERE birthday >= '1990-01-01' AND birthday <= '1999-01-01'

 

三、利用LIKE篩選資料

語法：

[ NOT ] LIKE pattern [ ESCAPE escape_character ]

萬用字元	描述	範例
%	含有任何零個或多個字元的字串	WHERE 書名 LIKE '%資料庫%' ---可查詢出所有書名中含有 '資料庫' 這三個字的任何書名資料
_ (底線)	含有任何單一字元	WHERE 書名 LIKE '%__庫%' ---可查詢出所有以'庫'結尾，且只有三個字的書名(例如「資料庫」或「知識庫」等書名)
[ ]	包含指定範圍中的任何單一字元	WHERE 城市 LIKE '[新台]北市' ---意指符合第一個字為「新」或「台」而第二第三個字為「北市」的城市名稱
[^]	排除指定範圍中的任何單一字元	WHERE 城市 LIKE '[^新台]北市' ---意指排除第一個字為「新」或「台」的字眼，符合而第二第三個字為「北市」的城市名稱，例如：全北市

 

四、利用ALL與ANY或SOME篩選資料

先行準備資料表

CREATE TABLE TEAM_A 
  ( 
     name NVARCHAR(10), 
     age  TINYINT, 
  ) 

go 

INSERT INTO TEAM_A (name , age) VALUES ('Brooke' , 30);
INSERT INTO TEAM_A (name , age) VALUES ('Mary' , 40);
INSERT INTO TEAM_A (name , age) VALUES ('Bill' , 50);
INSERT INTO TEAM_A (name , age) VALUES ('John' , 60);
INSERT INTO TEAM_A (name , age) VALUES ('Tom' , 70);


CREATE TABLE TEAM_B 
  ( 
     name NVARCHAR(10), 
     age  TINYINT, 
  ) 

go 

INSERT INTO TEAM_B (name , age) VALUES ('June' , 20);
INSERT INTO TEAM_B (name , age) VALUES ('Gimi' , 31);
INSERT INTO TEAM_B (name , age) VALUES ('Jave' , 41);
INSERT INTO TEAM_B (name , age) VALUES ('Brown' , 50);
INSERT INTO TEAM_B (name , age) VALUES ('Dell' , 60);

 

1、ALL

以下例子意思為列出每一個TEAM_A成員的年齡須大於每一個TEAM_B成員的年齡之TEAM_A成員，

發現其結果只有TEAM_A的John與Tom符合條件。

SELECT name,age FROM TEAM_A 

SELECT name,age FROM TEAM_B 

SELECT name,age 
FROM   TEAM_A 
WHERE  age >= ALL (SELECT age FROM TEAM_B)

 

2、ANY、SOME

以下例子意思為列出每一個TEAM_A成員的年齡須大於部份TEAM_B成員的年齡之TEAM_A成員，

關鍵字ANY與SOME是相同的，

此例會發現TEAM_B年紀最小的是20歲，只要TEAM_A隨便一個成員都可以大於20歲，

所以結果將會列出所有TEAM_A成員。

 

五、EXISTS

先準備一個ORDER資料表與先前的EMPLOYEE資料表做範例

CREATE TABLE [ORDER] 
  ( 
     no      INT NOT NULL, 
     name    [NVARCHAR](10) NOT NULL, 
     year    SMALLINT NOT NULL, 
     quarter TINYINT NOT NULL, 
     item    [NVARCHAR](10) NOT NULL, 
     price   [INT] NOT NULL, 
     amount  [INT] NOT NULL, 
     PRIMARY KEY (no) 
  ) 

go 

INSERT [ORDER] VALUES (1,'Bill',2005,1,'CPU',3000,1);
INSERT [ORDER] VALUES (2,'Bill',2005,2,'RAM',2000,4);
INSERT [ORDER] VALUES (3,'Bill',2006,4,'MB',2000,1);
INSERT [ORDER] VALUES (4,'Mary',2010,1,'SSD',2500,5);
INSERT [ORDER] VALUES (5,'Mary',2010,1,'LCD',6700,1);
INSERT [ORDER] VALUES (6,'Bryan',2015,1,'KB',500,2);
INSERT [ORDER] VALUES (7,'Bryan',2015,3,'ROUTER',1500,2);
INSERT [ORDER] VALUES (8,'Tim',2016,1,'CPU',12000,1);
INSERT [ORDER] VALUES (9,'Tim',2016,4,'MB',5000,1);

 

列出EMPLOYEE裡有去購買商品的員工有誰，

將會發現有購買行為的有Bill和Mary兩人。

SELECT no,name,profession,sex,birthday 
FROM   EMPLOYEE 

SELECT no,name,year,quarter,item,price,amount 
FROM   [ORDER] 

SELECT no,name,profession,sex,birthday 
FROM   EMPLOYEE 
WHERE  EXISTS (SELECT * 
               FROM   [ORDER] 
               WHERE  EMPLOYEE.name = [ORDER].name)

Tips：使用 in 或 exists 的時機

如果主查詢中的表較大，而子查詢得出的結果集記錄較少時應該用 in；

如果主查詢中的表較小，而子查詢得出的結果集記錄較大時應該用 exists。

其實什麼時候使用 in 或 exists 的時機，主要是由訪問順序來決定（這是效能變化的關鍵），

如果是 exists，那麼則主查詢先被訪問再來訪問子查詢；

如果是IN，則子查詢先被訪問，再來訪問主查詢，

所以我們會以快速訪問完的方式為目標。