hobby:development:sql:select:query_0003

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

Generazione dati di esempio

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
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

1
2
SELECT * FROM Table1;
SELECT * FROM Table2;

INNER JOIN

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

SEMI JOIN

1
2
3
4
5
6
7
8
SELECT *
FROM Table1 T1
WHERE EXISTS
(
    SELECT 1
    FROM Table2 T2
    WHERE T1.IdT2 = T2.IdT2
);

LEFT OUTER JOIN

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

LEFT OUTER JOIN with exclusion

1
2
3
4
5
SELECT
    *
FROM Table1 T1
    LEFT OUTER JOIN Table2 T2 ON T1.IdT2 = T2.IdT2
WHERE T2.IdT2 IS NULL;

LEFT ANTI SEMI JOIN

1
2
3
4
5
6
7
8
9
SELECT
    *
FROM Table1 T1
WHERE NOT EXISTS
(
    SELECT 1
    FROM Table2 T2
    WHERE T1.IdT2 = T2.IdT2   
);

RIGHT OUTER JOIN

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

RIGHT OUTER JOIN with exclusion

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

RIGHT ANTI SEMI JOIN

1
2
3
4
5
6
7
8
SELECT *
FROM Table2 T2
WHERE NOT EXISTS
(
    SELECT 1
    FROM Table1 T1
    WHERE T1.IdT2 = T2.IdT2
);

FULL OUTER JOIN

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

CROSS JOIN, the Cartesian product

1
2
3
SELECT *
FROM Table1 T1
    CROSS JOIN Table2 T2;

FULL OUTER JOIN with exclusion

1
2
3
4
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

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

CROSS APPLY

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

OUTER APPLY

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

Two INNER JOINS

1
2
3
4
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

1
2
3
4
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

1
2
3
4
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

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

EXCEPT

1
2
3
4
5
SELECT IdT2 as Id
    FROM Table1
EXCEPT
SELECT IdT2 as Id
    FROM Table2;

UNION

1
2
3
4
5
SELECT IdT2 as Id
    FROM Table1
UNION
SELECT IdT2 as Id
    FROM Table2;

INTERSECT

1
2
3
4
5
SELECT IdT2 as Id
    FROM Table1
INTERSECT
SELECT IdT2 as Id
    FROM Table2;

  • hobby/development/sql/select/query_0003.txt
  • Ultima modifica: 2021/01/07 17:44
  • da mauro.cortese