Inner join is the default join.
Outter join has three categories: left join, right join and full join.
Lateral join:
Lateral join can be (NOT must be) terribly expensive, depending on how big the preceiving table is and how expensive the subquery is. You are going to be running that subquery once per row.
Rows from:
Filling gaps in sequences:
Subquery elimination:
Semi join:
Anti join:
Semi joins and anti joins are optimization for these subqueries.
Another style of subquery elimination:
Here's the fundamental defference: this 'where exists' is going to run for every row in the outer table, which can be good because the 'where exists' will short-circuit the first time it finds a true value, not continuing looking through the bookmarks table.
A use case 'where exists' is good:
Combining queries: Union, intersect, Except
'UNION ALL' doesn't remove duplicates, so it is faster than 'UNION'. If you know there's no duplicate or want to see the duplicates, prefer 'UNION ALL'.
'INTERSECT ALL' is faster than 'INTERSECT', but doesn't remove duplicates.
'EXCEPT ALL' is faster than 'EXCEPT', but doesn't remove dupplicates.
Set generating functions:
generate_series()
unnest()
json_to_recordset()
jsonb_to_recordset()
The result is the same as above.
regexp_matches()
string_to_table():
Indexing joins:
When you create a primary key, a unique constraint in a parent table, that is enforced by an index. That index is automatically created in the parent table. However, when you create a foreign key, even a foreign key constraint, the index is not automatically created in the child table.
How to improve? Create an index for the foreign key (can be a composite index, depending on the query condition often used).
