Copyright © 2024 PawSQL
The types of SQL joins mainly include inner join, outer join (left outer join, right outer join, full outer join), and cross join. C.L. Moffatt explains them very nicely in his article Visual Representation of SQL Joins in a visual way. You will find the information you need in following diagram he presented in his article.
Copyright © 2024 PawSQL
If two query conditions are connected by OR, the database optimizer may not be able to use indexes to execute the query. For example, for the following SQL statement:
select * from customer where c_phone like '139%' or c_name = 'Ray'
Even if both fields have indexes, they can be rewritten as a UNION query to leverage the indexes and improve query performance:
Copyright © 2024 PawSQL
Join Elimination is a rewriting optimization in SQL that simplifies queries and improves query performance by removing joins from the query without affecting the final result. Typically, this optimization is used when a query contains a primary-foreign key join and only references the primary key columns of the main table.
Consider the following example:
select o.* from orders o inner join customer c on c.c_custkey=o.o_custkey
The orders table is joined with the customer table, and c_custkey is the primary key of the customer table. In this case, the customer table can be eliminated, and the rewritten SQL would be:
select * from orders where o_custkey
An IN subquery is a type of subquery that takes the following form.
(expr1, expr2...) [NOT] IN (SELECT expr3, expr4, ...)
An IN subquery can be rewritten as an equivalent correlated EXISTS subquery or inner join, which can create a extra filtering condition. If the filtering condition has an appropriate index or is recommended by the PawSQL index recommendation engine, better performance can be achieved.