Being a SQL nerd you've probably heard of joins.
SQL JOINS combine rows from different tables on related columns.
INNER JOIN:
This being the most common type of join, inner join combines rows from two tables based on a related column between them. Basically, a field that exists in both tables.
Sometimes you can just write it as JOIN.
LEFT JOIN:
Retrieves all records from the left table with the corresponding matches in the right table. If there is no match in the right table, NULL values are returned for values in the right column.
RIGHT JOIN:
Look at this as the opposite of Left join. Retrieves all records from the right table with the corresponding matches on the left table. Again, if no match found, NULL values are returned as well.
FULL OUTER JOIN:
This type of join returns all records from both tables regardless of a match or not.
For cases where there is no match, NULL values as well are returned for the columns without a corresponding row.
CROSS JOIN:
I usually think it is a special type of JOIN...No condition imposed. (ON clause)
It returns a cartesian product of two tables. combining each row from the first table with each row from the other table.
While on JOINS, there are two clauses that appear as much:
UNION VS UNION ALL
Used to combine results from two or more queries into a single result set.
UNION automatically removes the duplicates that arise between the queries.
The idea is to generate a unique result set.
Unlike UNION, UNION ALL does not remove duplicates. It simply combines all results, including duplicates if any.
This means UNION ALL is faster than UNION as it does not need to check for duplicates and eliminate.
Happy querying!
Top comments (0)