SELF JOIN and CROSS JOIN

Use one table in two roles or generate combinations.

UnderstandStudy an examplePractise

Understand the concept

SELF JOIN is a pattern, not a separate SQL keyword: reference a table twice through different aliases. Employee can play both employee and manager roles. A LEFT JOIN preserves the top-level employee without a manager.

CROSS JOIN combines every left row with every right row. Six customers and seven products create 42 combinations. This is useful for date–product grids but multiplies rows if used unintentionally. It is not a substitute for a missing relationship predicate.

WHEN TO USE IT

Use it for hierarchies, comparisons and controlled combination grids.

COMMON PITFALL

Failing to estimate Cartesian growth.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT e.FullName AS Employee, m.FullName AS Manager FROM lab.Employee AS e
LEFT JOIN lab.Employee AS m ON m.EmployeeId = e.ManagerId;

Rasim has no manager; Ada and Can report to him.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

List employees directly reporting to Ada.

Show the solution and explanation

Worked solution

SELECT e.FullName FROM lab.Employee AS e JOIN lab.Employee AS m ON m.EmployeeId = e.ManagerId WHERE m.FullName = N'Ada';

Returns Deniz and Ece.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.