T-SQL, JOIN, and aliases

T-SQL, JOIN types, aliases, calculated columns, and multi-table queries on StoreDB.

Beginner 48 min read SQLT-SQLJOIN

Lesson 7 / 7 0%

View path

T-SQL, JOIN, and aliases

Level: beginner · Reading time: about 40 to 50 minutes

Topics: T-SQL, SELECT, JOIN types, aliases, calculated columns, ON vs WHERE

What will we learn?

T-SQL and SELECT
JOIN to link store tables
INNER, LEFT, RIGHT, FULL, CROSS JOIN
table and column aliases
calculated columns and ON vs WHERE

1. Why JOIN?

Customer names live in Customers, orders in Orders, products in Products, lines in OrderItems. To answer who ordered what, we must join tables.

2. What is T-SQL?

Transact-SQL — Microsoft SQL Server’s extended SQL. This session focuses on reading from multiple tables.

3. Basic SELECT

sql

SELECT *
FROM Customers;

4. Specific columns

sql

SELECT FirstName, LastName
FROM Customers;

5. What is JOIN?

Link rows by a relationship — e.g. Customers.CustomerId = Orders.CustomerId.

6. JOIN types

INNER JOIN · LEFT JOIN · RIGHT JOIN · FULL JOIN · CROSS JOIN

7. INNER JOIN

Only rows that match on both sides.

sql

SELECT *
FROM Customers
INNER JOIN Orders
    ON Customers.CustomerId = Orders.CustomerId;

8. Reading INNER JOIN

FROM Customers — base table. INNER JOIN Orders — attach orders. ON — join condition.

9. INNER JOIN result

Ali and Sara with orders appear. Reza with no order is excluded. INNER JOIN = intersection only.

10. INNER JOIN mental model

INNER JOIN = overlapping area only

11. Pick columns in JOIN

Prefer explicit columns over SELECT *:

sql

SELECT
    Customers.FirstName,
    Customers.LastName,
    Orders.OrderId,
    Orders.OrderDate
FROM Customers
INNER JOIN Orders
    ON Customers.CustomerId = Orders.CustomerId;

12. What is an alias?

A short temporary name — Customers AS C

13. Table alias

sql

SELECT C.FirstName, C.LastName, O.OrderId
FROM Customers AS C
INNER JOIN Orders AS O
    ON C.CustomerId = O.CustomerId;

14. AS is optional

FROM Customers C and FROM Customers AS C both work — AS reads clearer.

15. Column alias

sql

SELECT FirstName AS Name
FROM Customers;

16. Persian column aliases

sql

SELECT
    FirstName AS [نام],
    LastName AS [نام خانوادگی]
FROM Customers;

17. Alias does not rename the table

Only in that query’s output — the real table/column name stays the same.

18. Calculated column

sql

SELECT ProductName, Price, Stock, Price * Stock
FROM Products;

19. Alias on calculated column

sql

SELECT ProductName, Price, Stock,
       Price * Stock AS TotalStockValue
FROM Products;

20. Line total example

Quantity * UnitPrice AS LineTotal

21. Join customer and order

sql

SELECT C.CustomerId, C.FirstName, C.LastName, O.OrderId, O.OrderDate
FROM Customers AS C
INNER JOIN Orders AS O
    ON C.CustomerId = O.CustomerId;

22. Join order and items

sql

SELECT O.OrderId, OI.ProductId, OI.Quantity
FROM Orders AS O
INNER JOIN OrderItems AS OI
    ON O.OrderId = OI.OrderId;

23. Join items and products

sql

SELECT OI.OrderId, P.ProductName, OI.Quantity
FROM OrderItems AS OI
INNER JOIN Products AS P
    ON OI.ProductId = P.ProductId;

24. Main question

Which customer ordered which products? — four tables chained together.

25. Full store query

sql

SELECT C.FirstName, C.LastName, O.OrderId, O.OrderDate,
       P.ProductName, OI.Quantity
FROM Customers AS C
INNER JOIN Orders AS O ON C.CustomerId = O.CustomerId
INNER JOIN OrderItems AS OI ON O.OrderId = OI.OrderId
INNER JOIN Products AS P ON OI.ProductId = P.ProductId;

26. Join chain logic

Customer → order (CustomerId) → order line (OrderId) → product (ProductId). Understand the chain.

27. Sample output

Ali — order 1001 — mouse ×2, keyboard ×1

28. INNER JOIN with calculation

sql

SELECT C.FirstName, O.OrderId, P.ProductName, P.Price, OI.Quantity,
       P.Price * OI.Quantity AS TotalPrice
FROM Customers AS C
INNER JOIN Orders AS O ON C.CustomerId = O.CustomerId
INNER JOIN OrderItems AS OI ON O.OrderId = OI.OrderId
INNER JOIN Products AS P ON OI.ProductId = P.ProductId;

29. LEFT JOIN

All rows from the left table — unmatched right side becomes NULL.

sql

SELECT C.FirstName, O.OrderId
FROM Customers AS C
LEFT JOIN Orders AS O
    ON C.CustomerId = O.CustomerId;

30. LEFT JOIN example

Reza has no order — OrderId is NULL but his name still appears.

31. INNER vs LEFT

INNER: customers with orders only · LEFT: all customers

32. Customers with no orders

sql

SELECT C.CustomerId, C.FirstName
FROM Customers AS C
LEFT JOIN Orders AS O ON C.CustomerId = O.CustomerId
WHERE O.OrderId IS NULL;

33. RIGHT JOIN

All rows from the right table are kept.

sql

SELECT C.FirstName, O.OrderId
FROM Customers AS C
RIGHT JOIN Orders AS O
    ON C.CustomerId = O.CustomerId;

34. LEFT or RIGHT?

In practice LEFT JOIN is more common — swap table order instead of RIGHT JOIN.

35. FULL JOIN

All rows from both sides — NULL where no match.

sql

SELECT C.FirstName, O.OrderId
FROM Customers AS C
FULL JOIN Orders AS O
    ON C.CustomerId = O.CustomerId;

36. OUTER JOIN summary

LEFT = all left · RIGHT = all right · FULL = both sides

37. INNER in one line

matches only

38. LEFT in one line

all left + matching right

39. RIGHT in one line

all right + matching left

40. FULL in one line

all rows from both sides

41. CROSS JOIN

Every row in the first table paired with every row in the second — no ON clause.

42. CROSS JOIN row count

3 × 4 = 12 rows

43. CROSS JOIN syntax

sql

SELECT C.FirstName, P.ProductName
FROM Customers AS C
CROSS JOIN Products AS P;

44. No ON in CROSS JOIN

Goal: every possible combination.

45. CROSS JOIN use case

Color × size — all combinations for a catalog matrix.

46. CROSS JOIN danger

100,000 × 100,000 rows — use with care.

47. Store alias convention

C = Customers · O = Orders · OI = OrderItems · P = Products

48. Meaningful aliases

Avoid meaningless A,B,C for many tables — C/O/OI/P is clearer.

49. Multiple column aliases

sql

SELECT C.FirstName AS CustomerFirstName,
       P.ProductName AS Product,
       P.Price * OI.Quantity AS TotalPrice
FROM Customers AS C
INNER JOIN Orders AS O ON C.CustomerId = O.CustomerId
INNER JOIN OrderItems AS OI ON O.OrderId = OI.OrderId
INNER JOIN Products AS P ON OI.ProductId = P.ProductId;

50. Table vs column alias

Table alias shortens table names · column alias labels output headers

51. Full report query

sql

SELECT C.FirstName + N' ' + C.LastName AS CustomerName,
       O.OrderId, P.ProductName, OI.Quantity, P.Price,
       OI.Quantity * P.Price AS TotalPrice
FROM Customers AS C
INNER JOIN Orders AS O ON C.CustomerId = O.CustomerId
INNER JOIN OrderItems AS OI ON O.OrderId = OI.OrderId
INNER JOIN Products AS P ON OI.ProductId = P.ProductId;

52. Concatenated full name

C.FirstName + N' ' + C.LastName AS FullName

53. Calculated column is not stored

Price * Quantity AS TotalPrice exists only in the query result — not as a stored column.

54. Filter one customer

sql

SELECT C.FirstName, O.OrderId, P.ProductName, OI.Quantity
FROM Customers AS C
INNER JOIN Orders AS O ON C.CustomerId = O.CustomerId
INNER JOIN OrderItems AS OI ON O.OrderId = OI.OrderId
INNER JOIN Products AS P ON OI.ProductId = P.ProductId
WHERE C.CustomerId = 1;

55. ON vs WHERE

ON — how tables join · WHERE — filter after the join

56. Query mental model

FROM → JOIN → ON → WHERE → SELECT

Session summary

T-SQL · JOIN types · aliases for tables and columns · calculated columns with AS

T-SQL, JOIN, and aliases

T-SQL, JOIN, and aliases

What will we learn?

Session 7 exercises