A quick overview of JOIN commands that Relational Databases have to offer
An important component of any relational database system is the relationship between tables. This relationship facilitates connecting two tables based on data that they ‘have in common’. This connection is accomplished using a JOIN, which is an operation that matches rows from one table to the rows in another. The matching is done in such a manner that the columns from both the tables placed side by side although they may have come from separate tables. It must be said though, the fewer tables being joined is the faster the query will operate. This article gives an introduction to joins and their uses in the relational database engine.
Let’s create tables: CUSTOMERS and ORDERS as follows:
End-to-End Database Development Courses
CUSTOMERS ( cust_id [PK] , cust_fname, cust_lname, phone, address, country);
ORDERS ( order_id [PK] , order_date, reqd_date, ship_date, cust_id [FK] );
Scenario: We need to see a list of customers and details of their respective orders.
This is the simplest type of join where each row in one table is matched with all other rows in another table. Only if the join condition evaluates to true a row is. The data in common, must be true for both tables involved in the JOIN.
SELECT customers.cust_fname, orders.order_id, orders.order_date FROM customers INNER JOIN orders ON customer.cust_id = orders.cust_id;
Interestingly enough (doesn’t work for all engines), you can also write an INNER JOIN query with a WHERE condition.
SELECT customers.cust_fname, orders.order_id, orders.order_date FROM customers, orders WHERE customers.cust_id = orders.cust_id;
This JOIN retrieves all records in the table that is on the (literally) LEFT side of the join condition AND…any columns that match from the table on the RIGHT side of the condition. So a so customer with no orders will be returned from the customer table, but NULL values are returned for the columns in the orders table for the rows that do not match the JOIN condition.
SELECT customers.cust_fname, orders.order_id, orders.order_date FROM customers LEFT JOIN orders ON customer.cust_id = orders.cust_id;
This JOIN does the inverse of the LEFT JOIN. It returns all records from the table on the right, with any rows that might match the condition from the left table. In the same way, NULL values are returned for the columns in the left table for the rows that do not match the JOIN condition.
SELECT customers.cust_fname, orders.order_id, orders.order_date FROM customers RIGHT JOIN orders ON customer.cust_id = orders.cust_id;
The LEFT and RIGHT JOIN statements are sometimes penned with an optional OUTER keyword:
End-to-End Database Development Courses
A cross join returns as many combinations of all the rows contained in the joined tables. That is,each row of the left table concatenated with every row in the right table. Therefore, if there are total M rows in Table A and there are N rows in Table B, a cross join will produce M x N rows. Two ways to accomplish this CROSS JOIN are as follows:
SELECT customers.cust_fname, orders.order_id, orders.order_date FROM customers CROSS JOIN orders;
SELECT customers.cust_fname, orders.order_id, orders.order_date FROM customers, orders;
A FULL JOIN allows you to join two tables completely, bringing back full rows where the JOIN condition is met and NULL values on the side with no matching rows.
SELECT customers.cust_fname, orders.order_id, orders.order_date FROM customers FULL JOIN orders ON customer.cust_id = orders.cust_id;
This query will attempt a LEFT and RIGHT JOIN in one, bring back full rows for customers with orders and NULLs where there is a customer, with no order…and then also bring back every single order, and NULLs for Orders that have no matching customer (a good query to audit your Point of Sales System maybe?). The FULL JOIN is also sometimes written with the OUTER keyword.
Typically, JOINs are used to establish a master-detail kind of data representation and are essential for pulling meaningful data from multiple related tables. Multiple tables can be joined in a singular select statement, but remember that the query will slow down for each table and more so, the number of records being returned.