The solution for the need of data extraction from multiple tables, whether it is for a purpose of comparison or to display them together, lies in JOINs.
There are three main types of joins as I mentioned in the article SELECT statement (decomposed) where I briefly describe all parts of SELECT statement. There are sub-types of joins applicable to all three main types 🙂
- INNER JOIN
- OUTER JOIN
- CROSS JOIN
Sub-types for the first two are
- LEFT
- RIGHT
Other types applicable to all main three types are
- EQUIJOIN – is based on columns equality
- NONEQUIJOIN – is based on columns non-equality
Another specific “type” (it is not an extra category) is SELF-JOIN which connect one table to itself.
I will now describe what are the possibilities and use cases for all of them and teach you how to use them and I will do it the best way – demonstrate it on examples with explanation.