A join is a query that combines rows from two or more tables, views, or materialized views.
Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition.
CROSS JOIN (Cartesian product) is the simplest join.
We can start with the simplest possible join -- the "cross join" (or Cartesian product). If we have two database tables consisting of information about CDs and musical artists:
A join simply multiplies the two tables together into a new virtual table. There are four members of the Artists table and seven members in the CDs table which will result in 28 (!) rows in the result. You can try this using the following syntax,
SELECT * FROM Artists, CDs
and you should see a result that looks like the following table:
This table is typically filtered using the WHERE clause, for example
SELECT * FROM Artists, CDs WHERE Artists.ArtistID=CDs.ArtistID