Rows from two tables can be combined using joins.
Inner Joins
Inner joins return only the rows that meet the specified condition in both tables.
# keyword INNER is optional
SELECT * FROM toys
INNER JOIN bricks
ON toy_id = brick_id;
# Oracle syntax
SELECT * FROM toys, bricks WHERE toy_id = brick_id;
Outer Joins
Outer joins return all the rows from one table, along with the matching rows from the other. Rows without a matching entry in the outer table return null for the outer table's columns.
An outer join can either be left or right, which determines which side of the join the table returns all rows for.
# keyword OUTER is optional
SELECT * FROM toys
LEFT OUTER JOIN bricks
ON toy_id = brick_id;
SELECT * from toys
RIGHT JOIN bricks
ON toy_id = brick_id;
Full Joins
A full join includes unmatched rows from both tables. This is not supported directly by MariaDB.
SELECT * FROM toys
FULL JOIN bricks
ON toy_id = brick_id;
Cross Joins
A cross join returns every row from the first table matched to every row in the second. This will always return the Cartesian product of the two table's rows: i.e. the number of rows in the first table times the number in the second.SELECT * FROM toys
CROSS JOIN bricks;
See also here
