Welcome to Software Development on Codidact!

Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.

Post History

87%

+12 −0

Q&A Are there best practices for sticking conditions in WHERE clauses vs the JOIN statement?

Lets say I have two tables, A and B and I need to join a subset of them. Is there best practices of sticking the conditions in the WHERE clause like this, SELECT * FROM A JOIN B on a.fk_b = b....

3 answers · posted 4y ago by Charlie Brumbaugh‭ · last activity 1y ago by billkarwin‭

Question mysql join

#4: Post edited by

ArtOfCode‭ · 2020-11-14T02:15:45Z (about 4 years ago)

Copy Link

Raw

Markdown

Lets say I have two tables, A and B and I need to join a subset of them.
Is there best practices of sticking the conditions in the WHERE clause like this,
~~```~~
SELECT *
FROM A
JOIN B on a.fk_b = b.pk
WHERE a.pk <10000
```
versus sticking the condition in the JOIN like this,
~~```~~
SELECT *
FROM A
JOIN B on a.fk_b = b.pk
AND a.pk <10000
```
For these, it doesn't make any difference in speed or results, but are there best practices for where to put the conditions?

Lets say I have two tables, A and B and I need to join a subset of them.
Is there best practices of sticking the conditions in the WHERE clause like this,
```sql
SELECT *
FROM A
JOIN B on a.fk_b = b.pk
WHERE a.pk <10000
```
versus sticking the condition in the JOIN like this,
```sql
SELECT *
FROM A
JOIN B on a.fk_b = b.pk
AND a.pk <10000
```
For these, it doesn't make any difference in speed or results, but are there best practices for where to put the conditions?

#3: Post edited by

Alexei‭ · 2020-11-13T19:09:51Z (about 4 years ago)
added tag

Copy Link

Raw

Markdown

Lets say I have two tables, A and B and I need to join a subset of them.
Is there best practices of sticking the conditions in the WHERE clause like this,
```
SELECT *
FROM A
JOIN B on a.fk_b = b.pk
WHERE a.pk <10000
```
versus sticking the condition in the JOIN like this,
```
SELECT *
FROM A
JOIN B on a.fk_b = b.pk
AND a.pk <10000
```
~~For these, it doesn't make any difference in speed or results, but are there best practices for where to put the conditions?~~

Lets say I have two tables, A and B and I need to join a subset of them.
Is there best practices of sticking the conditions in the WHERE clause like this,
```
SELECT *
FROM A
JOIN B on a.fk_b = b.pk
WHERE a.pk <10000
```
versus sticking the condition in the JOIN like this,
```
SELECT *
FROM A
JOIN B on a.fk_b = b.pk
AND a.pk <10000
```
For these, it doesn't make any difference in speed or results, but are there best practices for where to put the conditions?

#2: Post edited by

Charlie Brumbaugh‭ · 2020-11-12T21:11:36Z (about 4 years ago)

Copy Link

Raw

Markdown

#1: Initial revision by

Charlie Brumbaugh‭ · 2020-11-12T21:11:00Z (about 4 years ago)

Copy Link

Raw

Markdown

Are there best practices for sticking conditions in WHERE clauses vs JOIN conditions

Lets say I have two tables, A and B and I need to join a subset of them. 

Is there best practices of sticking the conditions in the WHERE clause like this,

```
SELECT * 
FROM A
JOIN B on a.fk_b = b.pk
WHERE a.pk <10000
```
versus sticking the condition in the JOIN like this,

```
SELECT * 
FROM A
JOIN B on a.fk_b = b.pk
AND a.pk <10000
```

For these, it doesn't make any difference in speed or results, but are there best practices for where to put the conditions?

mysql

Communities

Post History