|
Custom Search
| |
|
Using Inner Joins to Combine Data from Two Tables In a normalized relational database, individual tables provide limited data set as related information is usually stored in other tables where foreign keys are used to link the data together. For more information about database normalization, refer to article How to Design Relational Database. What is JOIN? To combine data from multiple tables to provide useful data to applications and end users, we use JOIN operations. A join operation combines data from two or more tables to produce a single result set that gives more meaningful information about a set of data. In other words, columns and rows from these tables are put together into one single result set. There are three types of joins: INNER JOIN, OUTER (LEFT JOIN or RIGHT JOIN), and CROSS JOIN (note that in MySQL, CROSS JOIN is the same as inner join. In other Database Management Systems such as Microsoft SQL Server, cross joins display every combination of all rows in the joined tables. This can be achieved in MySQL by using a join without a common column.) There are rules to follow when join tables.
Only standard SQLs In the tutorials about joins, only standard SQLs are used. MySQL has quite a few extensions to ANSI (American National Standards Institute) standard SQLs in join operations. They will not be covered in these tutorials. The purpose is to make your SQL code reusable to other Database systems without too many modifications. Using Inner Joins INNER JOIN is the default join type. When you use the keyword JOIN in your query, it defaults to INNER JOIN. INNER JOIN only returns rows in the tables that have matches found on the join column.
Practice #1: Join two tables. Products table in Northwind database only stores SupplierID which is a foreign key pointing back to SupplierID column in suppliers table. If we want to know the supplier's name for a product, we need to write a query to join with suppliers table to get this information. In this practice, a single result set is returned which displays product name and the supplier's name for each product. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. /*
Query result set - 77 rows returned: Practice #2: Join three tables. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. /*
Query result set - 77 rows returned: Practice #3: JOIN operation with WHERE clause. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. /*
Query result set - 11 rows returned: Practice #4: Using inner join to return every combination of all rows in the joined tables. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. /*
Query result set - 72 rows returned: Practice #5: Using Non-standard SQL in a join operation and it's not recommended. This query returns the same result as Practice #1. It uses non ANSI standard joins in the SQL and I don't recommend it. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page. /*
Query result set - 77 rows returned:
Happy Coding! Other tutorials in this category 1. Using Outer Joins to Combine Data from Two Tables |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |