SQL Joins

SQL joins are used to combine rows from two or more tables.

SQL JOIN

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

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.

Let's look at a selection from the "Orders" table:

Then, have a look at a selection from the "Customers" table:

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, if we run the following SQL statement (that contains an INNER JOIN):

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

Try it Yourself »

it will produce something like this:

Different SQL JOINs

Before we continue with examples, we will list the types of the different SQL JOINs you can use:

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables

SQL INNER JOIN Keyword

SQL INNER JOIN Keyword

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

PS! INNER JOIN is the same as JOIN.

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

And a selection from the "Orders" table:

SQL INNER JOIN Example

The following SQL statement will return all customers with orders:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Try it Yourself »

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the "Customers" table that do not have matches in "Orders", these customers will NOT be listed.

SQL LEFT JOIN Keyword

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

And a selection from the "Orders" table:

SQL LEFT JOIN Example

The following SQL statement will return all customers, and any orders they might have:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Try it Yourself »

Note: The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).

SQL RIGHT JOIN Keyword

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

PS! In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Orders" table:

And a selection from the "Employees" table:

SQL RIGHT JOIN Example

The following SQL statement will return all employees, and any orders they have placed:

Example

SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;

Try it Yourself »

Note: The RIGHT JOIN keyword returns all the rows from the right table (Employees), even if there are no matches in the left table (Orders).

SQL FULL OUTER JOIN Keyword

â?® Previous

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

SQL FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023< 0 Like 0 Dislike Follow 0 Please Enter a comment Submit Other Lessons for You Derived Tables Following are the signs those qualify a query to be a derived table: These are defined in FROM clause of an outer query. It is surrounded by parenthesis followed by AS clause to define the derived... Vivek Grover 0 0 0 SQL Server Row Count for all Tables in a Database ProblemI am a database consultant and one of my tasks involves getting the row counts from all the tables in the source database and comparing it against the corresponding table row counts in the target... Amitava Majumder 0 0 0 How To Minimize The Page Splits In Sqlserver To Improve The Performane Of Database? How to minimize the page splits in sqlserver to improve the performane of database? Page Splits: A page is 8Kbytes of data which can be index related, data related, large object binary (lob’s)... Amitava Majumder 1 0 0 Temp Table in SQL Server Temp Table in SQL Server The temp table in SQL Server can be created at the run-time, and perform all the operation that a normal table can do. There are two types of Temporary Tables in SQL Server: Local... Amitava Majumder 0 0 0 10 Study Tips to Achieve your Goals This is particularly true of students that are looking to make the most of their study time and get better grades at Organization. 1. Set Study Goals: There is lots of credible research suggesting that... Manoj Kumar Vishwakarma 5 0 0 Find MS SQL Development Training near you MS SQL Development Training in Bangalore MS SQL Development Training in Hyderabad MS SQL Development Training in Pune MS SQL Development Training in Chennai MS SQL Development Training in Delhi MS SQL Development Training in Mumbai MS SQL Development Training in Gurgaon MS SQL Development Training in Noida MS SQL Development Training in Kolkata MS SQL Development Training in Ghaziabad Looking for MS SQL Development Training? Learn from Best Tutors on UrbanPro. Book a Free Demo Are you a Tutor or Training Institute? Join UrbanPro Today to find students near you MS SQL Development Questions Need help regarding SQL Server and Access Database Connectivity over a network on ODBC. Can pay a good price. Thanks 5 Answers The email address you have entered is already registered with us. Please to register for this class. I am a Student I am a Tutor Name* Please enter your full name. Please enter institute name. Email* Please enter your email address. Phone* - Select - Please enter a valid phone number. Location* Please enter a pincode or area name. City* Please enter city name. Category* Please enter category. Gender* Male Female Please select your gender. Email ID/ Mobile No.* Please enter either mobile no. or email. Enter Password* Please enter OTP Please enter Password Sorry, this phone number is not verified, Please login with your email Id. By signing up, you agree to our Terms of Use and Privacy Policy. Already a member? X Looking for MS SQL Development Classes? The best tutors for MS SQL Development Classes are on UrbanPro Select the best Tutor Book & Attend a Free Demo Pay and start Learning Book a Free Demo Learn MS SQL Development with the Best Tutors The best Tutors for MS SQL Development Classes are on UrbanPro Book a Free Demo This website uses cookies We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy Accept All Decline All About UrbanPro.com Terms of Use Privacy Policy UrbanPro Bangalore Chennai Delhi Hyderabad Mumbai Pune Kolkata Gurgaon Ahmedabad Noida UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more ThinkVidya Learning Pvt Ltd © 2010-2024All Rights Reserved