hobby:development:sql:select:query_0003 [MLC Wiki]

T-SQL JOIN Types
(Schema dei diversi tipi di JOIN)

Generazione dati di esempio

USE DbTemp
GO
 
-- Table 1 (Persone)
IF OBJECT_ID(N'dbo.Table1', 'U') IS NOT NULL
    DROP TABLE dbo.Table1
 
CREATE TABLE dbo.Table1 (
  IdT1 INT NOT NULL DEFAULT (0),
  Name VARCHAR(50) NULL,
  IdT2 INT NULL,
  IdT3 INT NULL
)
ON [PRIMARY]
 
INSERT INTO dbo.Table1(IdT1, Name, IdT2, IdT3) VALUES
     (1, N'Mauro', 1, NULL)
    ,(2, N'Andrea', 3, NULL)
    ,(3, N'Marco', 2, NULL)
    ,(4, N'Franco', 1, NULL)
    ,(5, N'Giovanna', 7, NULL)
    ,(6, N'Francesca', 5, 2)
    ,(7, N'Giulia', NULL, 1)
    ,(8, N'Mario', NULL, 3)
 
 
-- Table 2 (Colori preferiti)
IF OBJECT_ID(N'dbo.Table2', 'U') IS NOT NULL
    DROP TABLE dbo.Table2
 
CREATE TABLE dbo.Table2 (
  IdT2 INT NOT NULL DEFAULT (0),
  Color VARCHAR(50) NULL
)
ON [PRIMARY]
 
INSERT INTO dbo.Table2(IdT2, Color) VALUES
     (1, N'Giallo')
    ,(2, N'Verde')
    ,(3, N'Rosso')
    ,(4, N'Arancio')
    ,(5, N'Grigio')
    ,(6, N'Nero')
    ,(7, N'Rosa')
    ,(8, N'Blue')
    ,(9, N'Azzurro')
 
-- Table 3 (Alimenti preferiti)
IF OBJECT_ID(N'dbo.Table3', 'U') IS NOT NULL
    DROP TABLE dbo.Table3
 
CREATE TABLE dbo.Table3 (
  IdT3 INT NOT NULL DEFAULT (0),
  Food VARCHAR(50) NULL
)
ON [PRIMARY]
 
INSERT INTO dbo.Table3(IdT3, Food) VALUES
     (1, N'Pizza')
    ,(2, N'Carne')
    ,(3, N'Pesce')

SELECT From Two Tables

SELECT * FROM Table1;
SELECT * FROM Table2;

INNER JOIN

SELECT * 
FROM Table1 T1
    INNER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2;

SEMI JOIN

SELECT *
FROM Table1 T1
WHERE EXISTS
(
	SELECT 1 
	FROM Table2 T2 
	WHERE T1.IdT2 = T2.IdT2
);

LEFT OUTER JOIN

SELECT *
FROM Table1 T1
    LEFT OUTER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2;

LEFT OUTER JOIN with exclusion

SELECT 
	*
FROM Table1 T1
	LEFT OUTER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2
WHERE T2.IdT2 IS NULL;

LEFT ANTI SEMI JOIN

SELECT 
	*
FROM Table1 T1
WHERE NOT EXISTS
(
	SELECT 1 
	FROM Table2 T2 
	WHERE T1.IdT2 = T2.IdT2    
);

RIGHT OUTER JOIN

SELECT *
FROM Table1 T1
	RIGHT OUTER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2;

RIGHT OUTER JOIN with exclusion

SELECT *
FROM Table1 T1
	RIGHT OUTER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2
WHERE T1.IdT2 IS NULL;

RIGHT ANTI SEMI JOIN

SELECT *
FROM Table2 T2
WHERE NOT EXISTS
(
	SELECT 1
	FROM Table1 T1
	WHERE T1.IdT2 = T2.IdT2
);

FULL OUTER JOIN

SELECT *
FROM Table1 T1
	FULL OUTER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2;

CROSS JOIN, the Cartesian product

SELECT *
FROM Table1 T1
	CROSS JOIN Table2 T2;

FULL OUTER JOIN with exclusion

SELECT *
FROM Table1 T1
	FULL OUTER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2
WHERE T1.IdT2 IS NULL OR T2.IdT2 IS NULL;

NON-EQUI INNER JOIN

SELECT * 
FROM Table1 T1
    INNER JOIN Table2 T2 ON T1.IdT2 >= T2.IdT2;

CROSS APPLY

SELECT *
FROM Table1 T1
	CROSS APPLY [SUBQUERY] AS T;

OUTER APPLY

SELECT *
FROM Table1 T1
	OUTER APPLY [SUBQUERY] AS T;

Two INNER JOINS

SELECT *
FROM Table1 T1
	INNER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2
	INNER JOIN Table3 T3 ON T1.IdT3 = T3.IdT3;

Two FULL OUTER JOINS

SELECT *
FROM Table1 T1
	FULL OUTER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2
	FULL OUTER JOIN Table3 T3 ON T1.IdT3 = T3.IdT3;

Two LEFT OUTER JOINS

SELECT *
FROM Table1 T1
	LEFT OUTER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2
	LEFT OUTER JOIN Table3 T3 ON T1.IdT3 = T3.IdT3;

INNER JOIN and a LEFT OUTER JOIN

SELECT *
FROM Table1 T1
	INNER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2
	LEFT OUTER JOIN Table3 T3 ON T1.IdT3 = T3.IdT3;

EXCEPT

SELECT IdT2 as Id
	FROM Table1
EXCEPT
SELECT IdT2 as Id
	FROM Table2;

UNION

SELECT IdT2 as Id
	FROM Table1
UNION
SELECT IdT2 as Id
	FROM Table2;

INTERSECT

SELECT IdT2 as Id
	FROM Table1
INTERSECT
SELECT IdT2 as Id
	FROM Table2;