Is every type of SQL JOIN a subset of CROSS JOIN

Question

I would like to confirm my understanding of what I have researched about JOINS. Is every
type of SQL JOIN a subset of CROSS JOIN ?

CROSS JOIN you are selecting Cartesian product, so if you have table A (100 rows) and
table B (100 rows) you get 100 x 100 = 10,000

All other JOINS and basically selecting data from the above set.

Like the INNER JOIN or just JOIN, which gives only those rows that are matching on a
specific column.

LEFT OUTER JOIN is giving all the null rows on the left including what comes out of INNER
JOIN.

Two questions

1. Is it possible to join on more than one column ? Assuming I have 2 columns combined together as my primary key

2. I am thinking JOIN results don't depend on the relation-ship of the tables? If you have one-to-one between 2 tables your CROSS join will result/return the same number of rows.

Answer 1

Not every type of SQL join returns a proper subset of a cross join. For example, a left join will return NULL for columns that don't match the join condition. But those rows won't appear in a cross join. Using PostgreSQL,

-- cross join . . .
with t1 as (
  select 1 as n, 'a' as s
  union all
  select 2, 'b'
), t2 as (
  select 1 as n, 'a' as s
)
select * 
from t1
cross join t2

1  a  1  a 
2  b  1  a

-- left outer join
with t1 as (
  select 1 as n, 'a' as s
  union all
  select 2, 'b'
), t2 as (
  select 1 as n, 'a' as s
)
select * 
from t1
left join t2 on t1.n = t2.n

1  a  1  a 
2  b

That second row isn't in the cross join.

Yes, you can join on more than one column.

select your-column-list
from table_1 t1
inner join table_2 t2
        on t1.first_column  = t2.first_matching_column
       and t1.second_column = t2.second_matching_column

You can also join on part of a column, or join on an inequality.

-- Joins the three-character column t1.first_column on the first
-- three characters of t2.first_matching_column. Exact syntax
-- depends on the DBMS, but most support something like this.
inner join table_2 t2
        on t1.first_column  = left(t2.first_matching_column, 3)

-- Joins on an inequality. Also called a non-equijoin or theta join.
inner join table_2 t2
        on t1.first_column  < t2.first_matching_column

And joins don't depend on the relationships (foreign key references) between tables. Joins depend only on common (or compatible) values. In most cases, if the joined columns have different data types, the dbms will probably try to cast one to the other. That might or might not work, depending on the types and values involved.