Inner Join Vs In

SELECT C.* FROM StockToCategory STC INNER JOIN Category C ON STC.CategoryID = C.CategoryID WHERE STC.StockID = @StockID VS SELECT * FROM Category WHERE CategoryID IN (SELEC

Solution 1:

The two syntaxes serve different purposes. Using the Join syntax presumes you want something from both the StockToCategory and Category table. If there are multiple entries in the StockToCategory table for each category, the Category table values will be repeated.

Using the IN function presumes that you want only items from the Category whose ID meets some criteria. If a given CategoryId (assuming it is the PK of the Category table) exists multiple times in the StockToCategory table, it will only be returned once.

In your exact example, they will produce the same output however IMO, the later syntax makes your intent (only wanting categories), clearer.

Btw, yet a third syntax which is similar to using the IN function:

Select ...
FromCategoryWhereExists    (
                Select 1
                From StockToCategory
                Where StockToCategory.CategoryId = Category.CategoryId
                    And StockToCategory.Stock = @StockId
                )

Solution 2:

Syntactically (semantically too) these are both correct. In terms of performance they are effectively equivalent, in fact I would expect SQL Server to generate the exact same physical plans for these two queries.

Solution 3:

T think There are just two ways to specify the same desired result.

Solution 4:

for sqlite

table device_group_folders contains 10 records

table device_groups contains ~100000 records

INNER JOIN: 31 ms

WITHRECURSIVE select_childs(uuid) AS (
SELECT uuid FROM device_group_folders WHERE uuid ='000B:653D1D5D:00000003'UNIONALLSELECT device_group_folders.uuid FROM device_group_folders INNERJOIN select_childs ON parent = select_childs.uuid
) SELECT device_groups.uuid FROM select_childs INNERJOIN device_groups ON device_groups.parent = select_childs.uuid;

WHERE 31 ms

WITHRECURSIVE select_childs(uuid) AS (
    SELECT uuid FROM device_group_folders WHERE uuid ='000B:653D1D5D:00000003'UNIONALLSELECT device_group_folders.uuid FROM device_group_folders INNERJOIN select_childs ON parent = select_childs.uuid
) SELECT device_groups.uuid FROM select_childs, device_groups WHERE device_groups.parent = select_childs.uuid;

IN <1 ms

SELECT device_groups.uuid FROM device_groups WHERE device_groups.parent IN (WITHRECURSIVE select_childs(uuid) AS (
    SELECT uuid FROM device_group_folders WHERE uuid ='000B:653D1D5D:00000003'UNIONALLSELECT device_group_folders.uuid FROM device_group_folders INNERJOIN select_childs ON parent = select_childs.uuid
) SELECT*FROM select_childs);