SQL Difference Between in and or in Where

SQL difference between IN and OR in WHERE

They are semantically identical.

IN is just a shorthand for a string of equality statements like in your second example. Performance should also be identical.

The type shouldn't matter, it will always evaluate to a string of equalities.

There is a difference when you are using NOT IN and data that can be NULL, though - a NULL will not evaluate false for a NOT IN comparison, so you may get rows you didn't expect in the result set.

As an example:

SELECT 'Passed!' WHERE NULL NOT IN ('foo', 'bar')

The above query will not return a row even though at face value NULL is neither 'foo' or 'bar' - this is because NULL is an unknown state, and SQL cannot say with certainty that the unknown value is NOT one of the IN listed values.

Difference between and and where in joins

Firstly there is a semantic difference. When you have a join, you are saying that the relationship between the two tables is defined by that condition. So in your first example you are saying that the tables are related by cd.Company = table2.Name AND table2.Id IN (2728). When you use the WHERE clause, you are saying that the relationship is defined by cd.Company = table2.Name and that you only want the rows where the condition table2.Id IN (2728) applies. Even though these give the same answer, it means very different things to a programmer reading your code.

In this case, the WHERE clause is almost certainly what you mean so you should use it.

Secondly there is actually difference in the result in the case that you use a LEFT JOIN instead of an INNER JOIN. If you include the second condition as part of the join, you will still get a result row if the condition fails - you will get values from the left table and nulls for the right table. If you include the condition as part of the WHERE clause and that condition fails, you won't get the row at all.

Here is an example to demonstrate this.

Query 1 (WHERE):

SELECT DISTINCT field1
  FROM table1 cd
  LEFT JOIN table2
    ON cd.Company = table2.Name
 WHERE table2.Id IN (2728);

Result:

field1
200

Query 2 (AND):

SELECT DISTINCT field1
  FROM table1 cd
  LEFT JOIN table2
    ON cd.Company = table2.Name
   AND table2.Id IN (2728);

Result:

field1
100
200

Test data used:

CREATE TABLE table1 (Company NVARCHAR(100) NOT NULL, Field1 INT NOT NULL);
INSERT INTO table1 (Company, Field1) VALUES
('FooSoft', 100),
('BarSoft', 200);

CREATE TABLE table2 (Id INT NOT NULL, Name NVARCHAR(100) NOT NULL);
INSERT INTO table2 (Id, Name) VALUES
(2727, 'FooSoft'),
(2728, 'BarSoft');

What is the difference between the IN operator and = operator in SQL?

IN

will not generate an error if you have multiple results on the subquery. Allows to have more than one value in the result returned by the subquery.

=

will generate an error if you have more than one result on the subquery.

• SQLFiddle Demo (IN vs =)

SQL Difference between using = and IN

Use IN if you're doing multiple or's e.g.

Where StudentID = 7 or StudentID = 6 or StudentID = 5

Would be

Where StudentID IN (5,6,7)

Otherwise just use =

IN vs OR in the SQL WHERE clause

I assume you want to know the performance difference between the following:

WHERE foo IN ('a', 'b', 'c')
WHERE foo = 'a' OR foo = 'b' OR foo = 'c'

According to the manual for MySQL if the values are constant IN sorts the list and then uses a binary search. I would imagine that OR evaluates them one by one in no particular order. So IN is faster in some circumstances.

The best way to know is to profile both on your database with your specific data to see which is faster.

I tried both on a MySQL with 1000000 rows. When the column is indexed there is no discernable difference in performance - both are nearly instant. When the column is not indexed I got these results:

SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);
1 row fetched in 0.0032 (1.2679 seconds)

SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)

So in this case the method using OR is about 30% slower. Adding more terms makes the difference larger. Results may vary on other databases and on other data.

Difference between where and and clause in join sql query

Base on the following two test result

select a.id, a.name,a.country from table a left join table b
on a.id = b.id
where a.name is not null 

is faster (237 Vs 460). As far as I know, it is a standard.

In SQL / MySQL, what is the difference between ON and WHERE in a join statement?

WHERE is a part of the SELECT query as a whole, ON is a part of each individual join.

ON can only refer to the fields of previously used tables.

When there is no actual match against a record in the left table, LEFT JOIN returns one record from the right table with all fields set to NULLS. WHERE clause then evaluates and filter this.

In your query, only the records from gifts without match in 'sentgifts' are returned.

Here's the example

gifts

1   Teddy bear
2   Flowers

sentgifts

1   Alice
1   Bob

---
SELECT  *
FROM    gifts g
LEFT JOIN
        sentgifts sg
ON      g.giftID = sg.giftID

---

1  Teddy bear   1     Alice
1  Teddy bear   1     Bob
2  Flowers      NULL  NULL    -- no match in sentgifts

---
SELECT  *
FROM    gifts g
LEFT JOIN
        sentgifts sg
ON      g.giftID = sg.giftID
WHERE   sg.giftID IS NULL

---

2  Flowers      NULL  NULL    -- no match in sentgifts

As you can see, no actual match can leave a NULL in sentgifts.id, so only the gifts that had not ever been sent are returned.

Difference between HAVING and WHERE in SQL

The simple way to think about it is to consider the order in which the steps are applied.

Step 1: Where clause filters data

Step 2: Group by is implemented (SUM / MAX / MIN / ETC)

Step 3: Having clause filters the results

So in your 2 examples:

SELECT agentId, SUM(quantity) total_sales 
FROM sales s, houses h
WHERE s.houseId = h.houseId AND h.type = "condo"
GROUP BY agentId
ORDER BY total_sales;

Step 1: Filter by HouseId and Condo

Step 2: Add up the results
(number of houses that match the houseid and condo)

SELECT agentId, SUM(quantity) total_sales 
FROM sales s, houses h
GROUP BY agentId
HAVING s.houseId = h.houseId AND h.type = "condo"
ORDER BY total_sales;

Step 1: No Filter

Step 2: Add up quantity of all houses

Step 3: Filter the results by houseid and condo.

Hopefully this clears up what is happening.

The easiest way to decide which you should use is:
- Use WHERE to filter the data
- Use HAVING to filter the results of an aggregation (SUM / MAX / MIN / ETC)

Difference between IN and ANY operators in SQL

SQL>
SQL> -- Use the ANY operator in a WHERE clause to compare a value with any of the values in a list.
SQL>

SQL> -- You must place an =, <>, <, >, <=, or >= operator before ANY.

SQL> SELECT *
  2  FROM employee
  3  WHERE salary > ANY (2000, 3000, 4000);

For In Operator

SQL> -- Use the IN operator in a WHERE clause to compare a value with any of the values in a list.
SQL> SELECT *
  2  FROM employee
  3  WHERE salary IN (2000, 3000, 4000);

But with the IN operator you cannot use =, <>, <, >, <=, or >=

Difference between inner join and where in select join SQL statement

One difference is that the first option hides the intent by expressing the join condition in the where clause.

The second option, where the join condition is written out is more clear for the user reading the query. It shows the exact intent of the query.

As far as performance or any other difference, there shouldn't be any. Both queries should return the exact same result and perform the same under most RDBMS.

---

Related Topics