SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Let's look at a selection from the "Orders" table:
| OrderID | CustomerID | OrderDate |
| 10308 | 2 | 1996-09-18 |
| 10309 | 37 | 1996-09-19 |
| 10310 | 77 | 1996-09-20 |
Then, look at a selection from the "Customers" table:
| CustomerID | CustomerName | ContactName | Country |
| 1 | Alfreds Futterkiste | Maria Anders | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mexico |
Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.
Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Try it Yourself »
and it will produce something like this:
| OrderID | CustomerName | OrderDate |
| 10308 | Ana Trujillo Emparedados y helados | 9/18/1996 |
| 10365 | Antonio Moreno Taquería | 11/27/1996 |
| 10383 | Around the Horn | 12/16/1996 |
| 10355 | Around the Horn | 11/15/1996 |
| 10278 | Berglunds snabbköp | 8/12/1996 |
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tablesLEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right tableRIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left tableFULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
W3schools Pathfinder
Track your progress - it's free!
FAQs
How many types of JOINs are there in SQL? There are four main types of JOINs in SQL: INNER JOIN, OUTER JOIN, CROSS JOIN, and SELF JOIN. However, remember that OUTER JOINS have two subtypes: LEFT OUTER JOIN and RIGHT OUTER JOIN.
How JOINs work in SQL? ›
The SQL JOIN is a command clause that combines records from two or more tables in a database. It is a means of combining data in fields from two tables by using values common to each table. If you're working with databases, at some point in your work you will likely need to use SQL JOINs.
When to use which join in SQL? ›
In general, you'll only really need to use inner joins and left outer joins. Which join type you use depends on whether you want to include unmatched rows in your results: If you need unmatched rows in the primary table, use a left outer join. If you don't need unmatched rows, use an inner join.
How to add 3 JOINs in SQL? ›
3-Table JOIN syntax.
- SELECT column-names.
- FROM table-name1.
- JOIN table-name2 ON column-name1 = column-name2.
- JOIN table-name3 ON column-name3 = column-name4.
- WHERE condition.
You can use UNION and UNION ALL from set operator to get data if you don't want used JOIN of two tables. Just make sure these conditions should match - Datatype of the column should be the same in expression if we are using UNION or UNION ALL operators.
How do I join two tables without having common columns? ›
1 Answer
- We can use the Cartesian product, union, and cross-product to join two tables without a common column.
- Cartesian product means it matches all the rows of table A with all the rows of table B. ...
- Union returns the combination of result sets of all the SELECT statements.
As tables get large, nested loop and hash joins can become costly. Large datasets quickly use up RAM and force the query planner to perform many expensive I/O operations to move data in and out of memory. For these cases, query planners will likely use a merge join. Like hash join, merge join consists of two steps.
How does SQL handle multiple joins? ›
SQL multiple joins for beginners with examples
- Inner join returns the rows that match in both tables.
- Left join returns all rows from the left table.
- Right join returns all rows from the right table.
- Full join returns whole rows from both tables.
In this step, you create the union query by copying and pasting the SQL statements.
- On the Create tab, in the Queries group, click Query Design.
- On the Design tab, in the Query group, click Union. ...
- Click the tab for the first select query that you want to combine in the union query.
The simplest and most common form of a join is the SQL inner join the default of the SQL join types used in most database management systems. It's the default SQL join you get when you use the join keyword by itself. The result of the SQL inner join includes rows from both the tables where the join conditions are met.
At the same time, they are a very complex topic that requires plenty of practice and experience, and knowledge of data to really understand them and use them properly. There's really nothing too complex behind the idea of JOINs.
Why use JOINs in SQL instead of WHERE? ›
In most cases implicit JOINs will act as INNER JOINs. If you want to use a JOIN other than an INNER JOIN stating it explicitly makes it clear what is going on. JOINing in the WHERE clause can be confusion since this is not it's typical purpose. It is most often used to filter the data.
How do I join all tables in SQL? ›
To join three or more tables using a subquery, you can use a nested SELECT statement to retrieve data from one table based on the values in another table, and then join the results of the subquery with the remaining tables in the outer SELECT statement.
Can we join 3 tables at a time in SQL? ›
Using JOIN in SQL doesn't mean you can only join two tables. You can join 3, 4, or even more! The possibilities are limitless. The best way to practice SQL JOINs is LearnSQL.com's interactive SQL JOINs course.
Can I join more than 2 tables in SQL? ›
It is possible to use multiple join statements together to join more than one table at the same time. To do that you add a second INNER JOIN statement and a second ON statement to indicate the third table and the second relationship.
What are common types of join in SQL? ›
The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.
What does 1 1 mean in SQL join? ›
When you use 1=1 as the condition, it essentially means where true . Since the equality condition 1=1 is always true, the WHERE clause does not filter out any records. In other words, it returns all the records from the table without any filtering.
What is the difference between join and inner join? ›
1. The join operation which is used to merge two tables depending on their same column name and data types is known as natural join. Inner joins have a specific join condition. Here, the join operation is used to form a new table by joining column values of two tables based upon the join-predicate.
