Are you a SQL Server DBA looking to enhance your knowledge of relational database operations? Looking for an introduction to SQL JOIN statements? Understanding how to use the SQL INNER JOIN can make navigating the world of and query data management much easier. In this blog post, we are going to provide an overview of what an Inner Join is and offer several examples demonstrating how one would be used in practical situations. Ready to get started? Let’s dive in!
Inner Join in T-SQL is a powerful relational operator to combine data from two different tables. It is commonly used to join together the common rows between two or more tables in an SQL environment. Inner join is essential for retrieving data from the multiple tables with related records and placing them into a single table. This type of inner join syntax returns only matching records from both tables based on the predefined relationship.
Inner Joins are useful for combining data from the multiple tables with related tables which can be used for analysis, summaries, and other important decisions. The resulting information makes it possible to easily understand how different sections of the database interact with each other. Overall, Inner Joins play an important role in the use of T-SQL by providing a convenient way to organize complex data while giving access to any correlated fields.
Here's An Example Of Creating The Customers And Orders Two Tables With Sample Data So We Can Demonstrate The Inner Join Clause.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL
);
INSERT INTO customers (customer_id, customer_name, email, phone) VALUES
(1, 'John Smith', 'john@example.com', '123-456-7890'),
(2, 'Jane Doe', 'jane@example.com', '555-555-1212'),
(3, 'Bob Johnson', 'bob@example.com', '555-123-4567');
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL,
product_id INT NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
INSERT INTO orders (order_id, order_date, customer_id, product_id, total_amount) VALUES
(1, '2022-02-15', 1, 1, 50.00),
(2, '2022-02-17', 1, 2, 100.00),
(3, '2022-02-18', 2, 3, 75.00),
(4, '2022-02-20', 3, 1, 200.00);The Basic Syntax For Inner Join In SQL Is As Follows:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;Two Tables To Be Joined
Here, table1 and table2 are the names of the two tables yet to be joined, and column_name is the name of the column that is common to both tables.
The INNER JOIN keyword is used to combine rows from both tables combining rows that have matching values in the specified columns. The ON keyword is used to specify the condition for the first inner join in sql*.
For example, let's say we have two tables, customers and orders, and we want to join them based on the customer_id column. The SQL query would look like this:
SELECT customers.customer_name, orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;
This would return a result set that includes first table with the customer name and order date for all orders placed by each customer in the customers table.
Difference between JOIN and INNER JOIN
In T-SQL (Transact-SQL), there is no difference between INNER JOIN and JOIN. They are synonymous and can be used interchangeably.
INNER JOIN and JOIN both perform the same function, which is to combine rows from two or more tables based on a common column or set of columns from second table. The INNER JOIN keyword explicitly specifies that only matching rows from both tables should be returned, whereas JOIN can be used as a shorthand for INNER JOIN.
So in summary, INNER JOIN and JOIN have the same functionality in T-SQL and can be used interchangeably.
SQL INNER JOIN Examples
here are some additional examples using the same tables and data, with an additional table query data:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Widget', 10.00),
(2, 'Gadget', 20.00),
(3, 'Thingamabob', 15.00);
Inner join all three tables:
Suppose we want to find all orders that have product information available, along with the customer information and product details. We can use inner join to combine selected columns from all three tables based on the common column values customer_id and product_id as follows:
SELECT c.customer_name, o.order_date, p.product_name, p.price, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN products p ON o.product_id = p.product_id;
Inner join with a where clause:
Suppose we want to find all orders made by customers with a phone number starting with "555", but only for the Widget product. We can use inner join with a WHERE clause as follows:
SELECT c.customer_name, o.order_date, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN products p ON o.product_id = p.product_id
WHERE c.phone LIKE '555%' AND p.product_name = 'Widget';Inner join with group by:
Suppose we want to find the total amount of orders made for each product. We can use inner join with GROUP BY as follows:
SELECT p.product_name, SUM(o.total_amount) as total_sales
FROM products p
INNER JOIN orders o ON p.product_id = o.product_id
GROUP BY p.product_name;
What is the difference between an Equi Join and inner Join ?
Equi join is a type of inner join where the join condition is based on equality between two columns from different tables. In other words, an equi join matches rows where the values in the join columns are equal. An inner join, on the other hand, is a join that returns only the rows that have matching values in the join clause of both tables being joined, but the join condition does not have to be based on equality alone.
Inner join can be performed using different types of join conditions, including equi join, but also non-equal comparisons, range comparisons, and more complex join conditions. Therefore, equi join is a specific type of inner join that uses the equality comparison operator (=) to match rows between tables based on the values in one or more columns of one table between participating tables.
Here are examples of both Equi Join and Inner Join using all the columns above tables and sample data:
Example of Equi Join:
Suppose we want to join select columns of the customers table with the orders table based on the common "customer_id" column:
SELECT *
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;This query will return all rows from both tables where the customer_id values match, as the matching rows specified by the join condition "customers.customer_id = orders.customer_id".
Example of Inner Join:
Suppose we want to join the customers table with the orders table based on the common "customer_id" column, but also want to the second table to include a third table, called "items", that has a foreign key reference to the orders table:
CREATE TABLE items (
item_id INT PRIMARY KEY,
item_name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
order_id INT NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
INSERT INTO items (item_id, item_name, price, order_id) VALUES
(1, 'Item 1', 10.00, 1),
(2, 'Item 2', 25.00, 1),
(3, 'Item 3', 50.00, 2),
(4, 'Item 4', 100.00, 4);
SELECT *
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id
INNER JOIN items
ON orders.order_id = items.order_id;
This query will return all rows from all three tables where the inner join clause and conditions are satisfied, namely "customers.customer_id = orders.customer_id" and "orders.order_id = items.order_id". This is an example of an Inner Join that includes three tables.