自己結合

組み合わせ

nameprice
りんご50
みかん100
ぶどう50
すいか80
レモン30
いちご100

(例1)2列の組み合わせ

SELECT P1.name AS name_1 ,P2.name AS name_2 FROM Products P1 ,Products P2 WHERE P1.name > P2.name ;

(例2)重複組み合わせ

SELECT P1.name AS name_1 ,P2.name AS name_2 FROM Products P1 ,Products P2 WHERE P1.name >= P2.name ;

(例3)3列の組み合わせ

SELECT P1.name AS name_1 ,P2.name AS name_2 ,P3.name AS name_3 FROM Products P1 ,Products P2 ,Products P3 WHERE P1.name > P2.name AND P2.name > P3.name ;

部分的に不一致なキーの検索

(例1)同じ家族だけど、住所が違うレコードを検索

namefamily_idaddress
田中太郎100東京都新宿区西新宿1-1-1
田中春子100東京都新宿区西新宿1-1-2
山田明200東京都新宿区西新宿4-1-1
山田冴子200東京都新宿区西新宿4-1-1
山田沙織200東京都新宿区西新宿4-1-1
SELECT DISTINCT A1.name ,A1.address FROM Addresses A1 ,Addresses A2 WHERE A1.family_id = A2.family_id AND A1.address <> A2.address ;

(例2)同じ値段だけど、商品名が違うレコードを検索

nameprice
りんご50
みかん100
ぶどう50
すいか80
レモン30
いちご100
SELECT DISTINCT P1.name ,P1.price FROM Products P1 ,Products P2 WHERE P1.price = P2.price AND P1.name <> P2.name ;

ランキング

(例1)

nameprice
りんご50
みかん100
ぶどう50
すいか80
レモン30
いちご100
SELECT P1.name ,P1.price ,(SELECT COUNT(P2.price) FROM Products P2 WHERE P2.price > P1.price ) + 1 AS rank FROM Products P1 ;