A JOIN clause is used to query and access data from multiple tables by using logical relationships between them.
Join syntax
SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;The INNER JOIN is a process that matches rows from the first table and the second table which have the same key (as defined by the ON constraint) to create a result row with the combined columns from both tables.
Like the INNER JOIN these three new joins have to specify which column to join the data on.
When joining table A to table B, LEFT JOIN simply include all rows from A, and the matching row is found in B RIGHT JOIN is the same, but reversed, keeping rows in B and matching rows found in A.FULL JOIN simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table.
INNER JOIN and JOIN both are same. You can use it as alternatively
Did you know?
You might see queries with these joins written as LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN, but the OUTER keyword is really kept for SQL-92 compatibility and these queries are simply equivalent to LEFT JOIN, RIGHT JOIN, and FULL JOIN respectively.
A short note on NULLs
An alternative to NULL values in your database is to have data-type appropriate default values, like 0 for numerical data, empty strings for text data, etc. But if your database needs to store incomplete data, then NULL values can be appropriate if the default values will skew later analysis (for example, when taking averages of numerical data).
you can test a column for NULL values in a WHERE clause by using either the IS NULL or IS NOT NULL constraint.
SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;