SQL Tutorial - How to Join to Derived Tables

Опубликовано: 06 Июнь 2022
на канале: BeardedDev

2,795

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 Tables in SQL S... .

#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;