Skip to content

PostgreSQL:Select

Operator

Operator

Description

=

Equal

>

Greater than

<

Less than

>=

Greater than or equal

<=

Less than or equal

<> or !=

Not equal

AND

Logical operator AND

OR

Logical operator OR

IN

Return true if a value matches any value in a list

BETWEEN

Return true if a value is between a range of values

LIKE

Return true if a value matches a pattern

IS NULL

Return true if a value is NULL

NOT

Negate the result of other operators

Timestamp

Join

SQL:Join 항목 참조.

Example

LIMIT 

-- 처음 10개의 Row를 반환
SELECT * FROM test LIMIT 10;

OFFSET 

-- 위 SQL과 아래의 SQL은 같은 결과
SELECT * FROM test LIMIT 10 OFFSET 0;

LIMIT and OFFSET 

-- 11번째 부터 10개의 Row를 반환.
SELECT * FROM test LIMIT 10 OFFSET 10;

Left join

Left join example: 

SELECT
    u.uid AS uid,
    pm.project_uid AS project_uid,
    gm.group_uid AS group_uid,
    CASE
        WHEN pm.permission_uid IS NOT NULL THEN pm.permission_uid
        ELSE gm.permission_uid
    END AS permission_uid
FROM
    recc_user u
    LEFT JOIN recc_project_member pm ON pm.user_uid=u.uid
    LEFT JOIN recc_project p ON p.uid=pm.project_uid 
    LEFT JOIN recc_group_member gm ON gm.user_uid=u.uid
ORDER BY u.uid;

참고로, 위에서 NULL 체크하는 CASE 문은 COALESCE함수로 대체할 수 있다.

NOT EXISTS

Often fastest in Postgres. 

SELECT ip 
FROM   login_log l 
WHERE  NOT EXISTS (
   SELECT  -- SELECT list mostly irrelevant; can just be empty in Postgres
   FROM   ip_location
   WHERE  ip = l.ip
   );

LEFT JOIN / IS NULL

Sometimes this is fastest. Often shortest. Often results in the same query plan as NOT EXISTS. 

SELECT l.ip 
FROM   login_log l 
LEFT   JOIN ip_location i USING (ip)  -- short for: ON i.ip = l.ip
WHERE  i.ip IS NULL;

EXCEPT / EXCEPT ALL

Short. Not as easily integrated in more complex queries. 

SELECT ip 
FROM   login_log

EXCEPT ALL  -- "ALL" keeps duplicates and makes it faster
SELECT ip
FROM   ip_location;

중복을 제거하고 싶지 않다면 EXCEPT ALL를 사용하면 된다.

NOT IN

Only good without NULL values or if you know to handle NULL properly. I would not use it for this purpose.

:틀:WARNING 

SELECT ip 
FROM   login_log
WHERE  ip NOT IN (
   SELECT DISTINCT ip  -- DISTINCT is optional
   FROM   ip_location
   );

CASE ~~ WHEN ~~ THEN ~~ ELSE ~~ END

조건식을 사용할 때 문법 

SELECT
    CASE
        WHEN
            user_name IS NOT NULL THEN user_name
        ELSE
            NULL
        END AS worker_name;

임시 테이블 JOIN 

select * from
  (
   <your first query here>
  ) tbl1
  join (
    <your second query here>
  ) tbl2
  on tbl1.c_project_Id = tbl2.c_project_Id
 and tbl1.c_projectphase_Id = tbl2.c_projectphase_Id -- you might add or
 and tbl1.c_projecttask_Id  = tbl2.c_projecttask_Id  -- remove join criteria 
 and tbl1.m_product_Id = tbl2.m_product_Id           -- here

특정 테이블의 전체 컬럼을 Select 결과에 추가

table.*과 같이 사용하면 된다: 

SELECT myTable.*, otherTable.foo, otherTable.bar...

See also

Favorite site