JOIN join vs subquery

by Ivan Gerasimenko

JavaScript

DECLARE @tb AS TABLE 
(
	Id INT IDENTITY(1,1) PRIMARY KEY
	,Val NVARCHAR(255)
)
DECLARE @sub_tb AS TABLE 
(
	Id INT IDENTITY(1,1) PRIMARY KEY
	,IdTb INT NOT NULL
	,Val NVARCHAR(255)
)

INSERT INTO @tb VALUES ('AAA');
INSERT INTO @tb VALUES ('BBB');

INSERT INTO @sub_tb VALUES (1,'AAA.a');
INSERT INTO @sub_tb VALUES (1,'AAA.b');
INSERT INTO @sub_tb VALUES (2,'BBB.a');
INSERT INTO @sub_tb VALUES (2,'BBB.b');

SELECT tb.Val --, *
FROM @sub_tb sub_tb
INNER JOIN @tb tb 
	ON sub_tb.IdTb = tb.Id
WHERE sub_tb.Val = 'BBB.a';

/*
SELECT tb.Val --, *
FROM @tb tb, @sub_tb sub_tb
WHERE sub_tb.IdTb = tb.Id
	and sub_tb.Val = 'BBB.a'; --*/


SELECT tb.Val --, *
FROM @tb tb
WHERE tb.Id =
	(SELECT sub_tb.IdTb 
	FROM @sub_tb sub_tb
	WHERE sub_tb.Val = 'BBB.a');