Tag: postgresql Tag: sql

  • l2018-06-29

テストで使ったテーブル

  • tbl1とtbl2
isao=# select * from tbl1;
 cd |   hizuke   |         memo         
----+------------+----------------------
 01 | 2018-06-29 | aaaaaaaaaaaaaaaaa
 02 | 2018-06-29 | bbbbbbbbbbb
 03 | 2018-06-29 | ccccccccccccccccccc
 04 | 2018-06-29 | ddddddddddddddddddd
 05 | 2018-06-29 | eeeeeeeeeeee
 06 | 2018-06-29 | fffffffffffff
 07 | 2018-06-29 | ggggggggggggggg
 08 | 2018-06-29 | hhhhhhhhhhhhhhhhhhhh
 09 | 2018-06-29 | iiiiiii
 10 | 2018-06-29 | hhhhhhhhhh
 11 | 2018-06-29 | jjjjjjjjjjj
 12 | 2018-06-29 | kkkkkkkkkkkkkkkkk
 13 | 2018-06-29 | llllllllllllllll
(13 rows)
 
isao=# select * from tbl2;                                                                                                      
 cd |   hizuke   |           memo           
----+------------+--------------------------
 11 | 2018-06-29 | 111111111111111111111111
 12 | 2018-06-29 | 2222222222222222
 13 | 2018-06-29 | 3333333333333333
 14 | 2018-06-29 | 4444444444444444
 15 | 2018-06-29 | 55555555555555555
 16 | 2018-06-29 | 666666666666666666666
 17 | 2018-06-29 | 777777777777777777777
 18 | 2018-06-29 | 88888888888888888
 19 | 2018-06-29 | 999999999999999
 20 | 2018-06-29 | 2020202020202020


INとEXSISTSを使って両方に一致するcdの情報をSELECT

IN 

isao=# select * from tbl1 where cd in (select cd from tbl2);
 cd |   hizuke   |       memo        
----+------------+-------------------
 11 | 2018-06-29 | jjjjjjjjjjj
 12 | 2018-06-29 | kkkkkkkkkkkkkkkkk
 13 | 2018-06-29 | llllllllllllllll
(3 rows)
isao=# select * from tbl2 where cd in (select cd from tbl1);
 cd |   hizuke   |           memo           
----+------------+--------------------------
 11 | 2018-06-29 | 111111111111111111111111
 12 | 2018-06-29 | 2222222222222222
 13 | 2018-06-29 | 3333333333333333
(3 rows)

EXSISTS 

isao=# select * from tbl1 where exists (select 1 from tbl2 where tbl1.cd=tbl2.cd);
 cd |   hizuke   |       memo        
----+------------+-------------------
 11 | 2018-06-29 | jjjjjjjjjjj
 12 | 2018-06-29 | kkkkkkkkkkkkkkkkk
 13 | 2018-06-29 | llllllllllllllll
(3 rows)




cdの内容が別のテーブルにない場合に表示する 

isao=# select * from tbl1 left outer join tbl2 on (tbl1.cd=tbl2.cd) where tbl2.cd is null;
 cd |   hizuke   |         memo         | cd | hizuke | memo 
----+------------+----------------------+----+--------+------
 01 | 2018-06-29 | aaaaaaaaaaaaaaaaa    |    |        | 
 02 | 2018-06-29 | bbbbbbbbbbb          |    |        | 
 03 | 2018-06-29 | ccccccccccccccccccc  |    |        | 
 04 | 2018-06-29 | ddddddddddddddddddd  |    |        | 
 05 | 2018-06-29 | eeeeeeeeeeee         |    |        | 
 06 | 2018-06-29 | fffffffffffff        |    |        | 
 07 | 2018-06-29 | ggggggggggggggg      |    |        | 
 08 | 2018-06-29 | hhhhhhhhhhhhhhhhhhhh |    |        | 
 09 | 2018-06-29 | iiiiiii              |    |        | 
 10 | 2018-06-29 | hhhhhhhhhh           |    |        | 
(10 rows)
isao=# select * from tbl1 right outer join tbl2 on (tbl1.cd=tbl2.cd) where tbl1.cd is null;
 cd | hizuke | memo | cd |   hizuke   |         memo          
----+--------+------+----+------------+-----------------------
    |        |      | 14 | 2018-06-29 | 4444444444444444
    |        |      | 15 | 2018-06-29 | 55555555555555555
    |        |      | 16 | 2018-06-29 | 666666666666666666666
    |        |      | 17 | 2018-06-29 | 777777777777777777777
    |        |      | 18 | 2018-06-29 | 88888888888888888
    |        |      | 19 | 2018-06-29 | 999999999999999
    |        |      | 20 | 2018-06-29 | 2020202020202020
(7 rows)
  • 並びが変わるが上記のLEFT OUTER JOINの例
isao=# select * from tbl2 left outer join tbl1 on (tbl1.cd=tbl2.cd) where tbl1.cd is null;
 cd |   hizuke   |         memo          | cd | hizuke | memo 
----+------------+-----------------------+----+--------+------
 14 | 2018-06-29 | 4444444444444444      |    |        | 
 15 | 2018-06-29 | 55555555555555555     |    |        | 
 16 | 2018-06-29 | 666666666666666666666 |    |        | 
 17 | 2018-06-29 | 777777777777777777777 |    |        | 
 18 | 2018-06-29 | 88888888888888888     |    |        | 
 19 | 2018-06-29 | 999999999999999       |    |        | 
 20 | 2018-06-29 | 2020202020202020      |    |        | 
(7 rows)