SQL in Query Produces Strange Result

SQL IN query produces strange result

The problem here is that you're not using Table.Column notation in your subquery, table Order doesn't have column ID and ID in subquery really means Person.ID, not [Order].ID. That's why I always insist on using aliases for tables in production code. Compare these two queries:

select * from Person WHERE id IN (SELECT ID FROM [Order]);

select * from Person as p WHERE p.id IN (SELECT o.ID FROM [Order] as o)

The first one will execute but will return incorrect results, and the second one will raise an error. It's because the outer query's columns may be referenced in a subquery, so in this case you can use Person columns inside the subquery.
Perhaps you wanted to use the query like this:

select * from Person WHERE pid IN (SELECT PID FROM [Order])

But you never know when the schema of the [Order] table changes, and if somebody drops the column PID from [Order] then your query will return all rows from the table Person. Therefore, use aliases:

select * from Person as P WHERE P.pid IN (SELECT O.PID FROM [Order] as O)

Just quick note - this is not SQL Server specific behaviour, it's standard SQL:

• SQL Server demo
• PostgreSQL demo
• MySQL demo
• Oracle demo

T-SQL - Weird Results when I do a SELECT

I think its the problem within the interface I am using to query the database. This database is externally hosted so provider has given us the interface to access that data and it seems there is a problem in that interface.

Thanks for everyone's help and suggestions.

SQL query ignores WHERE clausule and gives strange result

Try using a table alias (Sales2 is what I used below) inside the inline view to make sure it's referring to the correct table.

SELECT 
  agent, 
  (SELECT COUNT(*) 
   FROM Sales Sales2 
   WHERE Sales2.agent=Sales.agent 
     AND Sales2.finalized_at BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59'
  ) / (
   SELECT SUM(uren) 
   FROM Uren 
   WHERE datum BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59' 
     AND agent=Sales.agent
  ) as sph 
FROM Sales 
WHERE finalized_at BETWEEN '2020-01-04 00:00:00' AND '2020-01-04 23:59:59' 
GROUP BY agent 
ORDER by sph DESC;

Also, be careful about a possible divide by 0 if there aren't any associated records in Uren table.

SQL queries producing unexpected results

Imagine you have table1 containing a and b as separate rows, and table2 has the exact same contents.

Now for your second query, table1's row a will be compared to both the rows in table2. It will pass the ON clause when comparing to row b in table2, and hence a will be in your result set. Similarly for the b row in table1 which will pass the ON clause when compared to the a row in table2.

You could rewrite the query as

SELECT DISTINCT SomeCharValue
FROM TABLE1
WHERE SomeCharValue NOT IN (SELECT DISTINCT SomeCharValue FROM Table2)
ORDER BY SomeCharValue

---

Related Topics