Introduction
This article explains how to select data from multiple tables using the SQL LEFT OUTER JOIN operator.
Relational databases store data in multiple related tables. There are various approaches to selecting data from multiple tables, with SQL JOINS being the most common technique.
You can use different types of JOIN operators to extract data from two or more than two tables:
- INNER JOIN – returns only matching rows from tables participating in the JOIN operation
- OUTER JOIN – returns matching as well as non matching rows from both tables
- RIGHT OUTER – fetches all rows from the table on the right side of the JOIN operation while only matching rows are selected from the table on the left side.
- LEFT OUTER JOIN is the inverse of the RIGHT OUTER JOIN and selects all rows from the left table and only matching rows from the right table. LEFT OUTER JOIN is commonly referred to as LEFT JOIN.
In this article, you will study the following four approaches for applying LEFT OUTER JOIN in SQL Server:
● LEFT OUTER JOIN using SQLCmd utility
● LEFT OUTER JOIN using SSMS
● LEFT OUTER JOIN using dbForge Studio SQL Text Query Window
● LEFT OUTER JOIN using dbForge Studio Query Editor
Creating the Dummy Database
I will create a dummy dataset with three tables to demonstrate examples in this article. The following script creates a dummy dataset.
| Create Database Store USE Store |
| CREATE TABLE [Orders](Id INT PRIMARY KEY IDENTITY(1,1),Quantity INT,ProductID INT,CustomerID INT) CREATE TABLE Products(Id INT PRIMARY KEY ,Name VARCHAR (50) NOT NULL,Price FLOAT,) CREATE TABLE Customers(Id INT PRIMARY KEY,Name VARCHAR (50) NOT NULL,) |
The script below inserts records in the three tables in our dataset.
| INSERT INTO ProductsVALUES (1, ‘Tea’, 5.0),(2, ‘Milk’, 2.5),(3, ‘Chocolate’, 0.80),(7, ‘Cookies’, 1.20),(8, ‘Banana’, 1.40 ),(4, ‘Apple’, 3.50),(10,’Croissant’, 1.00),(12,’Bread’, 0.95),(6, ‘Coffee’, 4.00) INSERT INTO CustomersVALUES (1, ‘Sara’),(2, ‘Nick’),(3, ‘Jones’),(10, ‘Elis’),(12, ‘Mike’),(15, ‘Andy’) INSERT INTO OrdersVALUES ( 5, 1, 2),( 3, 2, 2),( 8, 3, 2),( 10, 3,1),( 7, 3,1),( 5, 4,1),( 8, 5,5),( 6, 5,6),( 10, 7,8) |
Syntax of LEFT OUTER JOIN
The syntax of the LEFT OUTER JOIN is straightforward. You can use a SELECT statement to specify the columns you want to select from both tables, followed by the FROM and LEFT JOIN or (OUTER LEFT JOIN) statements. The ON operator specifies the matching columns from both tables. The following script shows the syntax of the LEFT OUTER JOIN operation.
| SELECT left_table_name.column_name1, Left_table_name.column_name…N, right_table_name.column_name1, right_table_name.column_name…N, FROM left_table_nameLEFT JOIN right_table_nameON left_table_name.joining_column = right_table_name.joining_column |
LEFT OUTER JOIN Using SQLCMD Utility
You can JOIN two or more tables in SQL Server via the SQLCmd utility.
The SQLCmd tool is a command-line application that allows you to perform operations on SQL Server.
To run the SQLCmd utility in Windows:
- Enter the following command in the “Run” shell.
- Replace the “server_name” with your SQL Server instance name.
- Replace “E” with “-U your_user -P your_password” if you are not using Windows authentication to access an SQL Server instance.
| “sqlcmd -S server_name -E” |
You will see the following window. Enter your SQL Query in the following command prompt and type “Go” to execute the query.
As an example, let’s fetch values from the “Id” and “Quantity” columns of the “Orders” table and the “Name” column of the “Customers” table using the LEFT OUTER JOIN. The tables are joined using the “CustomerID” column from the “Orders” table and the “Id” column of the “Customers” table.
| SELECT Orders.Id as OrderID, Orders.Quantity, Customers.Name as CustomerNameFROM OrdersLEFT JOIN CustomersON Orders.CustomerID = Customers.Id |
The output below shows all rows from the “Orders” table and only matching rows from the “Customers” table. NULL values are returned for the rows in the “Customers” table when a match is not found between the two tables.
If you swap the positions of the “Orders” and “Customers” tables, you will see that all rows are returned from the “Customers” table, whereas only matching rows will be returned from the “Orders” table. Here is an example:
| SELECT Customers.Name as CustomerName, Orders.Id as OrderID, Orders.QuantityFROM CustomersLEFT JOIN OrdersON Customers.Id = Orders.CustomerID |
LEFT OUTER JOIN using SSMS Query Editor
You can employ SQL Server Management Studio (SSMS), a GUI utility for SQL Server, to JOIN two or more tables in SQL Server.
To do so, open SSMS and click “New Query” from the top menu.
A query window will open where you can enter your SQL query. As an example, I will apply the LEFT OUTER JOIN on the “Products” and “Orders” tables.
| SELECT Products.Name as ProductName, Orders.Id as OrderID, Orders.QuantityFROM ProductsLEFT JOIN OrdersON Products.Id = Orders.ProductID |
In the output, you can see all rows from the “Products” table and matching rows from the “Orders” table.
LEFT OUTER JOIN Using dbForge Studio for SQL Server
dbForge SQL Studio for SQL Server is a feature-rich IDE for SQL Server. dbForge Studio offers text and GUI-based query editors to perform advanced database operations on SQL Server. In addition, SQL Intellisense and code suggestion features of dbForge reduce SQL script writing time by 2 to 4 times.
Let’s see how to perform LEFT OUTER JOIN with dbForge Studio for SQL Server. But before that, I will briefly explain how to connect dbForge Studio for SQL Server with an SQL Server instance.
Connecting dbForge Studio with SQL Server
Open the dbForge Studio application; you will see the following dashboard. Click the “New Connection” button.
You will see the following window. Enter the SQL Server instance name you want to connect to and the authentication method to access the Server. You can also set the database name if you want. If you do not set the database name, all the databases on the SQL Server instance will be accessible. Click the “Connect” button.
LEFT OUTER JOIN Using SQL Text Query Window in dbForge Studio
You can use the dbForge SQL text query editor to LEFT OUTER JOIN two or more tables. Click the “New SQL” button from the top left corner of the dbForge Studio dashboard. A new text query Window will open.
The query window supports SQL Intellisense features. As you write the script, you will see automatic suggestions and code completion features, as shown in the following screenshot.
Enter your SQL query in the query window and click the “Execute” button from the top menu. The following script applies LEFT OUTER JOIN on the “Products” and “Orders” tables.
LEFT OUTER JOIN Using dbForge Studio Query Editor
dbForge Studio’s Query Editor allows you to design complex SQL queries using a GUI.
As an example, I will explain how to apply LEFT OUTER JOIN on two tables using the dbForge Studio Query Editor.
Click the “New Query” button from the top menu, as shown in the following screenshot.
From the “Database Explorer” on the left pane, drag and drop the tables that you want to join in the query editor.
At the bottom of the query editor, you will see several options for generating SQL queries. Click the “Join” button. Select the JOIN operation that you want to perform (LEFT OUTER JOIN in our case), and specify the tables on the left and right-hand side of the JOIN operation.
Once you select the tables to join, you will see the option to specify the columns on which you want to join the tables. As an example, I will join the “Products” and “Orders” tables on “Id” and “ProductID” columns, respectively.
Once you join the two tables, you will see a link between the tables, as shown in the screenshot below.
You can select table columns by ticking the checkboxes before the column names.
Finally, click the “Execute” button to execute the query. You should see the following output:
Conclusion
Join operations are handy when selecting data from multiple related tables. There are multiple ways to apply JOIN operations in SQL Server. While SQLCmd utility and SSMS are freely available options, dbForge stands out because of its exceptional features, such as SQL Intellisence, code completion, and a GUI base query editor.
