SQL 'And' or 'Or' Comes First

SQL 'AND' or 'OR' comes first?

AND has higher precedence than OR:

From Oracle 12.1 docs (precedence for AND and OR is consistent to at least version 7 - probably further but I don't have documentation links)

Prior versions:

• Version 8
• Version 7

Operator precedences are shown in the following list, from highest
precedence to the lowest. Operators that are shown together on a line
have the same precedence.

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
XOR
||, OR
= (assignment), :=

However I would HIGHLY encourage the use of parentheses both for clarity and to ENSURE that the operators are evaluated in the order that you intend.

SQL Logic Operator Precedence: And and Or

And has precedence over Or, so, even if a <=> a1 Or a2

Where a And b 

is not the same as

Where a1 Or a2 And b,

because that would be Executed as

Where a1 Or (a2 And b)

and what you want, to make them the same, is the following (using parentheses to override rules of precedence):

 Where (a1 Or a2) And b

Here's an example to illustrate:

Declare @x tinyInt = 1
Declare @y tinyInt = 0
Declare @z tinyInt = 0

Select Case When @x=1 OR @y=1 And @z=1 Then 'T' Else 'F' End -- outputs T
Select Case When (@x=1 OR @y=1) And @z=1 Then 'T' Else 'F' End -- outputs F

For those who like to consult references (in alphabetic order):

• Microsoft Transact-SQL operator precedence
• Oracle MySQL 9 operator precedence
• Oracle 10g condition precedence
• PostgreSQL operator Precedence
• SQL as understood by SQLite

Which performs first WHERE clause or JOIN clause

The conceptual order of query processing is:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

But this is just a conceptual order. In fact the engine may decide to rearrange clauses. Here is proof. Let's make 2 tables with 1000000 rows each:

CREATE TABLE test1 (id INT IDENTITY(1, 1), name VARCHAR(10))
CREATE TABLE test2 (id INT IDENTITY(1, 1), name VARCHAR(10))

;WITH cte AS(SELECT -1 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) d FROM
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t1(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t2(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t3(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t4(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t5(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t6(n))

INSERT INTO test1(name) SELECT 'a' FROM cte

Now run 2 queries:

SELECT * FROM dbo.test1 t1
JOIN dbo.test2 t2 ON t2.id = t1.id AND t2.id = 100
WHERE t1.id > 1

SELECT * FROM dbo.test1 t1
JOIN dbo.test2 t2 ON t2.id = t1.id
WHERE t1.id = 1

Notice that the first query will filter most rows out in the join condition, but the second query filters in the where condition. Look at the produced plans:

1 TableScan - Predicate:[Test].[dbo].[test2].[id] as [t2].[id]=(100)

2 TableScan - Predicate:[Test].[dbo].[test2].[id] as [t2].[id]=(1)

This means that in the first query optimized, the engine decided first to evaluate the join condition to filter out rows. In the second query, it evaluated the where clause first.

Behavior of SQL OR and AND operator

Unlike in some programming languages, you cannot count on short-circuiting in T-SQL WHERE clauses. It might happen, or it might not.

SQL Order By: Specific Value First, then Ordering?

Use a case expression to put Needs Response rows first. Then order by date descending:

order by case when status = 'Needs Response' then 0 else 1 end, date desc

MySQL - SELECT the name that comes first alphabetically

This is a simple aggegation:

SELECT continent, MIN(name) AS name
FROM world 
GROUP BY continent
ORDER by continent

MySql In an inner join does it matter which table comes first?

Instead of the following:

select a.postsTitle
from posts a
inner join bookmarks b
  on b.userId = a.userId
  and b.userId = :userId

You should consider formatting your JOIN in this format, using the WHERE clause, and proper capitalization:

SELECT p.postsTitle
FROM bookmarks b
INNER JOIN posts p
  ON p.userId = b.userId
WHERE b.userId = :userId

While it makes no difference (performance wise) to MySQL which order you put the tables in with INNER JOIN (MySQL treats them as equal and will optimize them the same way), it's convention to put the table that you are applying the WHERE clause to first. In fact, assuming proper indexes, MySQL will most likely start with the table that has the WHERE clause because it narrows down the result set, and MySQL likes to start with the set that has the fewest rows.

It's also convention to put the joined table's column first in the ON clause. It just reads more logically. While you're at it, use logical table aliases.

The only caveat is if you don't name your columns and instead use SELECT * like the following:

SELECT *
FROM bookmarks b
INNER JOIN posts p
  ON p.userId = b.userId
WHERE b.userId = :userId

You'll get the columns in the order they're listed in the query. In this case, you'll get the columns for bookmarks, followed by the columns for posts.

Most would say never use SELECT * in a production query, but if you really must return all columns, and you needed the columns from posts first, you could simply do the following:

SELECT p.*, b.*
FROM bookmarks b
INNER JOIN posts p
  ON p.userId = b.userId
WHERE b.userId = :userId

It's always good to be explicit about the returned result set.

WHERE clause better execute before IN and JOIN or after

It doesn't matter

Logical processing order is always honoured: regardless of actual processing order

INNER JOINs and WHERE conditions are effectively associative and commutative (hence the ANSI-89 "join in the where" syntax) so actual order doesn't matter

Logical order becomes important with outer joins and more complex queries: applying WHERE on an OUTER table changes the logic completely.

Again, it doesn't matter how the optimiser does it internally so long as the query semantics are maintained by following logical processing order.

And the key word here is "optimiser": it does exactly what it says

SQL Order of execution vs Order of writing

SQL is a declarative language, not a procedural language. That means that the SQL compiler and optimizer determine what operations are actually run. These operations typically take the form of a directed acyclic graph (DAG) of operations.

The operators have no obvious relationship to the original query -- except that the results it generates are guaranteed to be the same. In terms of execution there are no clauses, just things like "hash join" and "filter" and "sort" -- or whatever the database implements for the DAG.

You are confusing execution with compilation and probably you just care about scoping rules.

So, to start with SQL has a set of clauses and these are in a very specified order. Your question contains this ordering -- at least for a database that supports those clauses.

The second part is the ordering for identifying identifiers. Basically, this comes down to:

• Table aliases are defined in the FROM clause. So this can be considered as "first" for scoping purposes.
• Column aliases are defined in the SELECT clause. By the SQL Standard, column aliases can be used in the ORDER BY. Many databases extend this to the QUALIFY (if supported), HAVING, and GROUP BY clauses. In general, databases do not support them in the WHERE clause.
• If two tables in the FROM have the same column name, then the column has to be qualified to identify the table. The one exception to this is when the column is a key in a JOIN and the USING clause is used. Then the unqualified column name is fine.
• If a column alias defined in the SELECT conflicts with a table alias in a clause that supports column aliases, then it is up to the database which to choose.

---

Related Topics