Home >> SQL JOINS

SQL JOINS

PDF

Joins and Keys

Sometimes we have to select data from two or more tables to make our result complete. We have to perform a JOIN. For example, linking companies to contacts in the CRM database.

Tables in a database can be related to each other with keys or ids. A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.

In the sales_company table, the companyid is the primary key, meaning that no two rows can have the same companyid. The companyid distinguishes two persons even if they have the same name.

Look carefully at the sales_company and sales_contacts tables. Notice that:

• The companyid column is the primary key of the sales_company table
• The contactid column is the primary key of the sales_contacts table
• The companyid column in the sales_contacts table is used to refer to the companies on the sales_company table without using their names

Referring to Two Tables

We can select data from the two tables by referring to two tables, like this:

Link the sales_company table to the sales_contacts table, i.e. connecting contacts to their associated company.

SELECT sales_company.company_name, sales_contacts.first_name FROM sales_company, sales_contacts WHERE sales_company.companyid = sales_contacts.companyid

Using Joins

OR we can select data from two tables with the JOIN keyword, like this:

SELECT sales_company.company_name, sales_contacts.first_name FROM sales_company INNER JOIN sales_contacts ON sales_company.companyid = sales_contacts.companyid

The INNER JOIN returns all rows from both tables where there is a match. If there are no rows in the sales_company that do not have matches in the sales_contacts, those rows will NOT be listed.

LEFT JOIN

List all companies and their contacts, if any:

SELECT sales_company.company_name, sales_contacts.first_name FROM sales_company LEFT JOIN sales_contacts ON sales_company.companyid = sales_contacts.companyid

The LEFT JOIN returns all the rows from the first table (on the LEFT side on the JOIN statement which is sales_company), even if there are no matches in the second table (sales_contacts). If there are rows in the sales_company table that do not have matches in the sales_contacts table, those rows ALSO will be listed.

RIGHT JOIN

List all contacts, and their companies, if any (note: this query should never have to be used in the CRM system as contacts should ALWAYS have a company):

SELECT sales_company.company_name, sales_contacts.first_name FROM sales_company RIGHT JOIN sales_contacts ON sales_company.companyid = sales_contacts.companyid

The RIGHT JOIN returns all the rows from the second table (the one on the right side of the JOIN statement which is sales_contacts), even if there are no matches in the first table (sales_company). If there had been any rows in the sales_contacts table that did not have matches in the sales_company table, those rows ALSO would have been listed.