select from self without FROM-clause in WHERE?

• shadow_2

  Right there with Babe

  Points: 773

  More actions

  May 9, 2016 at 6:13 am

  #310468

  Hi.

  I saw a strange looking query today when my colleague used a wrong alias in a where clause.

  I wonder why Query #2 and #3 work and when that behaviour could be useful. Someone got an idea?

  --TEMP-TABLES AND SAMPLE DATA

  IF OBJECT_ID('tempdb..#d1') IS NOT NULL

  DROP TABLE #d1

  CREATE TABLE #d1

  (

  hpnr INT,

  artnr INT,

  vorhanden BIT

  )

  INSERT INTO #d1

  ( hpnr, artnr, vorhanden )

  VALUES ( 1,1,0 ),(1,2,1),(2,1,1),(2,2,1),(3,1,0),(3,2,1)

  IF OBJECT_ID('tempdb..#d2') IS NOT NULL

  DROP TABLE #d2

  CREATE TABLE #d2 (hpnr INT)

  INSERT INTO #d2

  ( hpnr)

  VALUES ( 1),(2)

  --QUERIES

  --#1 (this was intended, restrict to only hpnr that exist in #d2)

  SELECT dk.hpnr,

  dk.artnr,

  dk.Vorhanden

  FROM#d1 as dk

  WHEREdk.hpnr IN (SELECT hpnr FROM #d2 AS d2)

  --#2 strange these both work anyway, when would you need this "SELECT FROM SELF" behaviour?

  SELECT dk.hpnr,

  dk.artnr,

  dk.Vorhanden

  FROM#d1 as dk

  WHEREdk.hpnr IN (SELECT dk.hpnr) --without any FROM-clause?

  --#3

  SELECT dk.hpnr,

  dk.artnr,

  dk.Vorhanden

  FROM#d1 as dk

  WHEREdk.hpnr IN (SELECT dk.hpnr FROM #d2 AS d2) --with FROM, but of course still wrong results

  DROP TABLE #d1

  DROP TABLE #d2

  Thanks in advance

  Steffen

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 9, 2016 at 6:26 am

  #1877450

  Your observations arise from the necessity to support queries such as this:

  SELECT

  dk.hpnr,

  dk.artnr,

  dk.Vorhanden

  FROM#d1 as dk

  WHERE EXISTS (SELECT 1 FROM #d2 d2 WHERE d2.hpnr = dk.hpnr)

  The lesson? Always use table aliases when there's more than one table referenced by a query.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden