SQL Tutorial - How to Join to Derived Tables

In this SQL Tutorial learn to how to join to derived tables. I talk you through the benefits of derived tables, show you to create a derived table then how to join to the derived table. In the video we work through practical examples, creating the same query with a join between two tables then grouping and creating a derived table then joining to give the same results.
If you are not familiar with derived tables then you can check out this video on my channel: • How to create Derived ... .
#sqltutorials #sqlserver #dataengineering #data
If you would like to follow along with the video you can use the code examples below:
CREATE TABLE dbo.Customers
(
CustomerKey INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Customers_CustomerKey PRIMARY KEY (CustomerKey),
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
BusinessName VARCHAR(50) NULL,
CustomerType CHAR(1)
);
INSERT INTO dbo.Customers (FirstName, LastName, BusinessName, CustomerType)
VALUES
('Albert', 'Gunner', NULL, 'P'),
(NULL, NULL, 'Beach Store', 'B'),
('Catherine', 'Smith', NULL, 'P'),
(NULL, NULL, 'Duncan''s Hair', 'B'),
('Erin', 'Fairclough', NULL, 'P'),
(NULL, NULL, 'Gaming Zone', 'B'),
('Henry', 'Long', NULL, 'P');
CREATE TABLE dbo.Orders
(
OrderKey INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Orders_OrderKey PRIMARY KEY (OrderKey),
CustomerKey INT NULL,
OrderDate DATE NULL,
OrderAmount DECIMAL(8, 2)
);
INSERT INTO dbo.Orders (CustomerKey, OrderDate, OrderAmount)
VALUES
(1, '20220501', 1000.00),
(1, '20220602', 9500.00),
(2, '20220501', 3000.00),
(2, '20220602', 3000.00),
(3, '20220501', 12000.00),
(3, '20220602', 6000.00),
(3, '20220501', 4000.00),
(4, '20220602', 7000.00),
(4, '20220501', 9000.00),
(4, '20220602', 10000.00),
(4, '20220501', 6000.00),
(5, '20220602', 8000.00),
(5, '20220501', 8000.00),
(6, '20220602', 22000.00),
(7, '20220501', 3000.00),
(7, '20220602', 2000.00);
-- create query using GROUP BY
SELECT
Cust.CustomerKey,
Cust.FirstName,
Cust.LastName,
Cust.BusinessName,
Cust.CustomerType,
COUNT(OrderKey) AS NoOfOrders,
SUM(OrderAmount) AS TotalAmount
FROM dbo.Customers AS Cust
INNER JOIN dbo.Orders AS Ord
ON Cust.CustomerKey = Ord.CustomerKey
GROUP BY
Cust.CustomerKey,
Cust.FirstName,
Cust.LastName,
Cust.BusinessName,
Cust.CustomerType;
-- create query using DERIVED TABLE
SELECT
Cust.CustomerKey,
Cust.FirstName,
Cust.LastName,
Cust.BusinessName,
Cust.CustomerType,
Ord.NoOfOrders,
Ord.TotalAmount
FROM dbo.Customers AS Cust
INNER JOIN
(
SELECT
CustomerKey,
COUNT(OrderKey) AS NoOfOrders,
SUM(OrderAmount) AS TotalAmount
FROM dbo.Orders
GROUP BY
CustomerKey
) AS Ord
ON Cust.CustomerKey = Ord.CustomerKey;

[@BeardedDevData]

Do you prefer the GROUP BY or Derived Table approach?

[@pavelgonzalez6254]

Derived table definitely does the right job!

[@superfreiheit1]

Derived Table

[@cj7334]

I'm new to learning SQL Server and TSQL and I love your videos. Please keep up the great lessons coming!

[@BeardedDevData]

Thanks so much, always great to hear when people start learning.

[@mudasir989]

Hi Breaded Dev can you make a video on what type of careers an SQL developer get into. Really like your content.

[@BeardedDevData]

Excellent idea, I will upload one over the next couple of weeks.

[@TimeHater420]

Should have used a better example. These literally produce the same results. It's opinionated which looks more complex. In my opinion, the derived table join is a more advanced technique so it looks more complex than just a basic select statement..

[@BeardedDevData]

I understand what you mean, probably not the greatest example but this is also the most common use case for derived tables, apply some manipulation to a table then join to another table, if you can grasp that, derived tables are actually quite straightforward. They are table expressions alongside views, CTEs and inline table valued functions.

[@gotchihaeyo1825]

I prefer JOIN

[@BeardedDevData]

Yes but in some cases, joining to a table wouldn't get you to the solution which is why we need to use derived tables.