SQL : joncţiuni

Lucrăm cu Microsoft SQL Server 2014 Enterprise (thanks, MSDNAA).

Joncţiuni.

Descărcaţi codulPentru vizualizare, folosiţi scrollbar-ul din josul blocului de cod.
/*
    --- jonctiunea incrucisata (CROSS JOIN):
	------ in limbaj matematic, ea desemneaza produsul cartezian; 
	       atentie, ea NU ESTE un produs cartezian ci un INNER JOIN fara clauza ON...
		   (a se vedea avertizarea care apare in diagrama de la Execution Plan);
        ------ vezi "https://en.wikipedia.org/wiki/Join_%28SQL%29#Cross_join";
	--- rezultatul operatiei este un tabel virtual (virtual table);
	--- utilizare frecventa: producerea de tabele auxiliare, d.ex. tabele de numere;
	--- trebuie evitata folosirea lui " SELECT * " aici deoarece se formeaza volume mari de date!
*/

/*
   --- testam ordinea factorilor in CROSS JOIN folosind:
   ------ o vedere (VIEW) si un tabel;
*/
USE [master];

IF DB_ID(N'testare_join') IS NOT NULL
BEGIN
PRINT N'Baza exist' + NCHAR(259) + N'!';
RETURN;
END;
GO

USE [master];
CREATE DATABASE testare_join;
GO

USE [testare_join];

IF OBJECT_ID(N'dbo.tabel','U') IS NOT NULL
BEGIN
PRINT N'Tabelul exist' + NCHAR(259) + N'!';
RETURN;
END;
GO

USE [testare_join];

CREATE TABLE dbo.tabel
(
   id INT IDENTITY NOT NULL,
   nume NVARCHAR(1000) DEFAULT(N'Popescu') NOT NULL
);
GO

USE [testare_join];

INSERT INTO dbo.tabel(nume)
VALUES(N'Georgescu')
,(N'Co' + NCHAR(351) + N'covescu')
,(N'Matache')
,('Donose');
GO

USE [testare_join];

SELECT *
FROM dbo.tabel;
GO


USE [testare_join];

IF OBJECT_ID(N'dbo.VedereTabel') IS NOT NULL
BEGIN
PRINT N'Vederea exist' + NCHAR(259) + N'!';
RETURN;
END;
GO

USE [testare_join];
GO
CREATE VIEW dbo.VedereTabel
AS
(
 SELECT
    2 * T.id AS vedereid --trebuie dat un pseudonim ("alias") care sa desemneze NUMELE COLOANEI!
	,T.nume
 FROM dbo.tabel AS T
);
GO

USE [testare_join];

SELECT *
FROM dbo.VedereTabel;
GO

USE [testare_join];
--tabele virtuale, vezi Itzik Ben-Gan, "T-SQL Fundamentals 2008", Microsoft Press, Redmond, 2009
SELECT *
FROM
(
  VALUES(N'termen',100)
  ,(N'intrebare',123)
  ,(N'salut',-34)
) AS TabelVirtual(cuvant,numar) --forma EXTERNA de aliasing (Ben-Gan, p. 168)
ORDER BY TabelVirtual.cuvant;
GO

USE [testare_join];
--tabele virtuale, sintaxa veche
SELECT *
FROM
(
  SELECT N'termen' AS cuvant,100 AS numar --forma INLINE de aliasing (Ben-Gan, ibid.)
  UNION ALL
  SELECT N'intrebare',123
  UNION ALL
  SELECT N'salut',-34
) AS TabelVirtual
ORDER BY TabelVirtual.cuvant;
GO

--testam ordinea factorilor:
-- au loc diferente?

USE [testare_join];
/*
   --- jonctiune incrucisata:
   ------ rezultatul jonctiunii este un TABEL VIRTUAL (Ben-Gan, p. 103)
   ------ coloanele acestui tabel virtual au NUMELE date de 
          numele coloanelor din tabelele care fac jonctiunea (Ben-Gan, ibid.)
*/
SELECT *
FROM (dbo.VedereTabel AS VT --parantezele sunt optionale
CROSS JOIN
dbo.tabel AS T); --parantezele sunt optionale
GO

USE [testare_join];

SELECT *
FROM (dbo.tabel AS T
CROSS JOIN
dbo.VedereTabel AS VT);
GO

USE [testare_join];

SELECT *
FROM (dbo.VedereTabel AS VT1
CROSS JOIN
dbo.VedereTabel AS VT2);
GO

USE [testare_join];

SELECT *
FROM (dbo.tabel AS T1
CROSS JOIN
dbo.tabel AS T2);
GO