Join

Syntax for joining on a key

Most common is to use JOIN ... ON

...
inner join T1 on T2.id = T1.id

We can also use the USING clause, which is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s)

• For example, the following produces the join condition ON T1.a = T2.a AND T1.b = T2.b.

select * from T1
inner join T2 using (a, b)

Doing it like this also suppresses redundant columns

• there is no need to print both of the matched columns, since they must have equal values

Anti-join

An anti-join is meant to keep only the rows that fail a test.

• ex. imagine we have a result set of students having taken an Economics exam. If we want to filter to include only those students who did not finish the exam (denoted by null value in the score column), we can filter out those who did in fact finish the exam.
  • note: since WHERE clause is just a filter, it doesn't care which columns are actually returned by the query. This is why we can just say SELECT 1. Postgres doesn't even look at what was selected; only that something was returned from the query.

select *
    from students
    inner join results on students.id = results.student_id
where not exists (
    select 1
        from results r
    where score is not null
)

---

Children

1. Cross
2. Lateral
3. Outer Join
4. Self Join

---

Backlinks

• Apache Flink
• View