oracle中exists和not exists用法举例详解

select * from A where not exists(select * from B where A.id = B.id);
select * from A where exists(select * from B where A.id = B.id);

SELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID) 

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１) 
--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据 

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2) 
--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据 

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3) 
--->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据

SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)

EXISTS = IN,意思相同不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因 
SELECT ID,NAME FROM A　 WHERE　ID IN (SELECT AID FROM B) 

NOT EXISTS = NOT IN ,意思相同不过语法上有点点区别 
SELECT ID,NAME FROM A WHERE　ID　NOT IN (SELECT AID FROM B) 

SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID = 1 OR CategoryID= 4 OR CategoryID = 5 

SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID IN (1, 4, 5) 

SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHEREroyaltyper <50) 

SELECT DISTINCT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id= publishers.pub_id AND type = 'business') 
SELECT distinct pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHEREtype = 'business') 

SELECT pub_name FROM publishers WHERE NOT EXISTS (SELECT * FROM titles WHERE pub_id =publishers.pub_id AND type = 'business') 

SELECT title FROM titles WHERE NOT EXISTS (SELECT title_id FROM sales WHERE title_id =titles.title_id) 

USE Northwind 
GO 
SELECT CategoryName 
FROM Categories 
WHERE EXISTS (SELECT NULL) 
ORDER BY CategoryName ASC 
GO 

USE pubs 
GO 
SELECT DISTINCT pub_name 
FROM publishers 
WHERE EXISTS 
    (SELECT * 
    FROM titles 
    WHERE pub_id = publishers.pub_id 
    AND type = \'business\') 
GO 

-- Or, using the IN clause: 

USE pubs 
GO 
SELECT distinct pub_name 
FROM publishers 
WHERE pub_id IN 
    (SELECT pub_id 
    FROM titles 
    WHERE type = \'business\') 
GO 

USE pubs 
GO 
SELECT au_lname, au_fname 
FROM authors 
WHERE exists 
    (SELECT * 
    FROM publishers 
    WHERE authors.city = publishers.city) 
GO 

-- Or, using = ANY 

USE pubs 
GO 
SELECT au_lname, au_fname 
FROM authors 
WHERE city = ANY 
    (SELECT city 
    FROM publishers) 
GO 

USE pubs 
GO 
SELECT title 
FROM titles 
WHERE EXISTS 
    (SELECT * 
    FROM publishers 
    WHERE pub_id = titles.pub_id 
    AND city LIKE \'B%\') 
GO 

-- Or, using IN: 

USE pubs 
GO 
SELECT title 
FROM titles 
WHERE pub_id IN 
    (SELECT pub_id 
    FROM publishers 
    WHERE city LIKE \'B%\') 
GO 

USE pubs 
GO 
SELECT pub_name 
FROM publishers 
WHERE NOT EXISTS 
    (SELECT * 
    FROM titles 
    WHERE pub_id = publishers.pub_id 
    AND type = \'business\') 
ORDER BY pub_name 
GO