SQL) SQL, Subqueries

This post was migrated from Tistory. You can find the original here.

You combine from, order by, and group by with commas.

select * from A, B

select course_id, week, count(c.likes) from checkins c 
group by course_id, week

Consecutive joins

a inner join b on ~~
inner join c on ~~

This joins c onto the table produced by joining a and b.

• When you want to compute statistics including rows that don’t exist (i.e., missing data), you need a left join.
• count doesn’t count null values.
• In a union, individual order by clauses don’t apply per-branch. You can only apply order by to the whole result.

Subqueries

Subqueries in the select clause, the join clause, and the from clause.

select *, (subquery select ~~) from ~~

a inner join (subqeuery) //subquery for the table to join
select tb.~ from (subqeury) as tb //subquery for the table to pull from

Multi-row queries

where ~ in (subquery returning multiple rows)
where ~ > (subquery returning multiple rows)

These are used with roughly this kind of nuance.

• When there’s no join in place and no column available to filter on, this can usually still be solved with a subquery in the where clause.

Functions

select *, substring_index(u.email, '@', -1) from user u

select *, substring(u.created_at, 1, 10) from user u

With a select item built through a function like the ones above,

select *, substring(u.created_at, 1, 10) ss from user u
group by ss

you can use it in group by or where clauses.

case when

(1)
select * from (
    select pu.point_user_id, pu.point,
    ( case 
    when pu.point > (select avg(pu.point) from point_users pu) then '평균 이상이예요!'
    else '평균 아래에요ㅠㅠ' end ) as lv
    from point_users pu
) a

You can change the value of a select item depending on the when condition.

(2)
select * from (
    select pu.point_user_id, pu.point,
    ( case 
    when pu.point > avg(pu.point) then '평균 이상이예요!'
    else '평균 아래에요ㅠㅠ' end ) as lv
    from point_users pu
) a

And if you write (1) the way it’s written in (2), you end up with only a single row.

(3)
select *, avg(pu.point) from point_users pu

The same thing happens in (3).
For (3), you can think of it as: since avg(pu.point) produces a single row, everything else collapses to match that single row.
And in (2), you can see that even when the aggregate isn’t shown in the select list and is only used in a condition, it still collapses the result to a single row.

Keep in mind: functions that produce a single row will collapse the result down to one row even if they’re used in a where clause rather than in the select list, not just when they’re displayed.
If you want to attach the result of a single-row function to each individual row, use a subquery as in (1).