![\"An](\"https:\/\/www.jquery-az.com\/wp-content\/uploads\/2018\/04\/SQL-JOINs.jpg\")
<\/p>\n<\/p>\n
Why use JOINs in SQL?<\/strong><\/p>\n In relational databases, we store data in various tables. For example, in a departmental store database, the product information is stored in one table, and order information in another table.<\/p>\n Similarly, employees’ information working in that store is stored in another table. However, the information in all these tables is related to each other.<\/p>\n For example, an order is placed by an employee that contains product information.<\/p>\n <\/p>\n Similarly, the sto_order<\/em> table stores the employee _id and product_id<\/em>. On the basis of those IDs, we may get detailed information about employees and products from the sto_employee<\/em> and sto_products<\/em>\u00a0tables, respectively. The JOIN clause enables us to do that easily.<\/p>\n See the examples of JOINs in SQL which is followed by its commonly used types and links to their respective tutorials.<\/p>\n<\/div>\n To explain the JOIN clause, I will use a few demo tables with dummy data. The following tables will be used in this and the coming examples and understanding how these are related will help in understanding JOIN and its various types.<\/p>\n In the first example, we will use a simple join and retrieve the complete data from sto_employees and sto_orders. Both tables are related by the employee ID.<\/p>\n The JOIN Query:<\/strong><\/p>\n From the above graphic, you can see the data of two tables. The third recordset is the result of using the JOIN query that retrieves the data based on matches found for employee ID.<\/p>\n The sto_orders is the right table<\/strong> (the table used after the JOIN clause). Remember these, as it will help in understanding the JOIN types.<\/p>\n The SQL has four types of JOINs. Depending on the requirement of the recordset, you may choose which one to choose.<\/p>\n Following are the join types with a little description which is followed by an example of each:<\/p>\n This is the default JOIN. If you simply use the JOIN keyword, it is taken as INNER JOIN.<\/p>\n In the above example, you might notice that tbl_employee contains more employee data. However, it returned only those records that are matched with the emp_id in the sto_orders table.<\/p>\n This is also called LEFT JOIN.<\/p>\n Also known as RIGHT JOIN, returns all records from the right table and only matches records from the left table.<\/p>\n The FULL OUTER JOIN or FULL JOIN fetches all records from both tables.<\/p>\n You have already seen an example of INNER JOIN. The following section shows examples of the other JOIN types.<\/p>\n The LEFT JOIN returns all matched or unmatched results from the left table while only matched records from the right table.<\/p>\n The LEFT JOIN Query:<\/strong><\/p>\n I used a table alias in this example. You may notice the NULL values for the orders tables (right table) for the unmatched records of the left table (employees).<\/p>\n This query shows how RIGHT JOIN works in SQL.<\/p>\n The Query:<\/strong><\/p>\n Use the FULL OUTER JOIN or FULL JOIN for using this type. This should return all records from the left and right tables:<\/p>\n The Query:<\/strong><\/p>\n As such, the st_orders<\/em> table must contain an emp_id<\/em> for each record so no mismatch is found for that table.<\/p>\n After the JOIN clause, you may use the WHERE clause<\/a> to filter the results based on certain criteria.<\/p>\n For specifying a column name in the WHERE clause<\/a>, use the table name or alias for referring to a column if that column exists in both tables. Otherwise, you may use the column without the table name prefix.<\/p>\n See this query of JOIN with WHERE clause:<\/p>\n The following examples use an INNER JOIN on two tables just like the above examples and group the result based on employee names after getting the SUM of salaries.<\/p>\n Have a look at this combination with the GROUP BY clause<\/a>:<\/p>\n The query:<\/strong><\/p>\n The result:<\/strong><\/p>\n Just like simply using the GROUP BY clause with HAVING, you may use it with the JOIN clause as well. To demonstrate that, I just added the SQL HAVING clause<\/a> in the above query and specified an expression to return those records with the sum of salary greater than 3000.<\/p>\nIn RDBMS, the SQL JOIN clause is the way to combine records from different tables. The tables are joined by logical relationships.<\/strong><\/div>\n--Simple JOIN\r\nSELECT * FROM sto_employees\r\nJOIN sto_orders ON sto_employees.id=sto_orders.emp_id\r\n\r\n--LEFT JOIN\r\nSELECT customers.customer_id, customers.customer_name, orders.order_id\r\nFROM customers\r\nLEFT JOIN orders ON customers.customer_id = orders.customer_id;\r\n\r\n<\/pre>\n
The example of SELECT with JOIN clause<\/strong><\/h3>\n
\n
\n\n
SELECT * FROM sto_employees\r\n\r\nJOIN sto_orders\u00a0 ON sto_employees.id=sto_orders.emp_id<\/pre>\n
![\"SQL](\"https:\/\/www.jquery-az.com\/wp-content\/uploads\/2018\/04\/8-1-SQL-JOIN.png\")
<\/p>\nNote that<\/strong>, in the above query the sto_employees is the left table (The table used in the SELECT statement<\/a>)<\/strong><\/div>\nSQL JOIN types<\/strong><\/h3>\n
The INNER JOIN<\/strong><\/h4>\n
The INNER JOIN only returns the matched records from both tables.<\/div>\nLEFT OUTER JOIN<\/strong><\/h4>\n
The LEFT JOIN retrieves all records from the left<\/strong> table and only matches records from the right<\/strong> table.<\/div>\nRIGHT OUTER JOIN<\/strong><\/h4>\n
FULL OUTER JOIN<\/strong><\/h4>\n
An example of LEFT JOIN<\/strong><\/h3>\n
SELECT * FROM sto_employees SE\r\n\r\nLEFT JOIN sto_orders SO\u00a0 ON SE.id=SO.emp_id<\/pre>\n
![\"SQL](\"https:\/\/www.jquery-az.com\/wp-content\/uploads\/2018\/04\/8-2-SQL-LEFT-JOIN.png\")
<\/p>\nThe example of RIGHT JOIN<\/strong><\/h3>\n
SELECT * FROM sto_employees SE\r\n\r\nRIGHT JOIN sto_orders SO\u00a0 ON SE.id=SO.emp_id<\/pre>\n
![\"SQL](\"https:\/\/www.jquery-az.com\/wp-content\/uploads\/2018\/04\/8-3-SQL-RIGHT-JOIN.png\")
<\/p>\nThe FULL OUTER JOIN example<\/strong><\/h3>\n
SELECT * FROM sto_employees SE\r\n\r\nFULL OUTER JOIN sto_orders SO\u00a0 ON SE.id=SO.emp_id<\/pre>\n
![\"SQL](\"https:\/\/www.jquery-az.com\/wp-content\/uploads\/2018\/04\/8-4-SQL-FULL-OUTER-JOIN.png\")
<\/p>\nThe example of using WHERE clause with JOIN<\/strong><\/h3>\n
SELECT * FROM sto_employees SE\r\n\r\nJOIN sto_orders SO\u00a0 ON SE.id=SO.emp_id\r\n\r\nWHERE emp_salary>=3500;<\/pre>\n
![\"SQL](\"https:\/\/www.jquery-az.com\/wp-content\/uploads\/2018\/04\/8-5-SQL-JOIN-WHERE.png\")
<\/p>\nThe Example of using GROUP BY clause with JOIN<\/strong><\/h3>\n
SELECT SUM(emp_salary),emp_name FROM sto_employees SE\r\n\r\nJOIN sto_orders SO\u00a0 ON SE.id=SO.emp_id\r\n\r\nGROUP BY emp_name;<\/pre>\n
![\"SQL](\"https:\/\/www.jquery-az.com\/wp-content\/uploads\/2018\/04\/8-6-SQL-JOIN-GROUP-BY.png\")
<\/p>\nAdding the HAVING clause in the above example<\/strong><\/h3>\n
![\"SQL](\"https:\/\/www.jquery-az.com\/wp-content\/uploads\/2018\/04\/8-7-SQL-JOIN-HAVING.png\")
<\/p>\n![\"SQL](\"https:\/\/www.jquery-az.com\/wp-content\/uploads\/2018\/04\/8-8-SQL-JOIN-ORDER-BY.png\")
<\/p>\n","protected":false},"excerpt":{"rendered":"