When storing information in a database, sometimes it will be necessary to link two tables that store information that is related.
There are multiple types of joins in SQL, but the main two categories are INNER JOIN and OUTER JOIN.
INNER JOIN:
In the simplest way, inner joins will only keep the information from the tables that is related. If you picture a Venn diagram, the inner join will only keep the information that is inside of the diagram.
The syntax for this is:
SELECT * FROM table1
JOIN table2
ON relation;
OUTER JOIN:
Outer joins will link the two tables, while also keeping unrelated data. There are three main types of outer joins in SQL: LEFT JOIN, RIGHT JOIN, and FULL JOIN.
LEFT JOIN will keep the unrelated data from the left (first) table. Picturing a Venn diagram, this join will keep the data is that is in the overlapping part of the diagram, as well as the data in the outside left circle. This is most commonly used.
The syntax for a LEFT JOIN is:
SELECT columns
FROM table1
LEFT JOIN table2
ON relation;
RIGHT JOIN will link the related data of the two tables, while keeping the unrelated data of table two (right table). The syntax for a RIGHT JOIN is the same as LEFT JOIN, but using the word 'right' instead.
A FULL JOIN will join the related information of two tables, while keeping the unrelated information.
The syntax for a FULL JOIN is:
SELECT columns
FROM table1
FULL JOIN table2
ON relation;
Top comments (1)
The textbook definition of Outer Join (in the case of left join) is that it returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
However, the only acceptable use is if you want your query results to include parent records that does not have child records (i,e, master records without details). Not the other way around. Other way around means you have orphaned records, which means child records without parent records (i.e, details without master records).
Sadly, too many of database that I have seen have this problem and that is why their database is not reliable and what is supposedly unnecessary data manipulation becomes necessary, which contributes to long term performance issues which are difficult to resolve