The correct way of joining two tables

Question

Lets say i have two tables:

TABLE001 - > employees

ID | EMPLOYEE | EMPLOYEENAME  | GENDER
1  | 000001   | MARK          | M
2  | 000002   | SARA          | F
3  | 000003   | MIKE          | M

TABLE002 - > logins

ID | DATE     | EMPLOYEE 
1  | 20200720 | 000001   
2  | 20200720 | 000002   
3  | 20200720 | 000003

And i want a query to display only results from TABLE002 where EMPLOYEEs gender is F.

Select * from TABLE002 as A
join TABLE001 as B on B.EMPLOYEE = A.EMPLOYEE
where B.GENDER = 'F'

or

Select * from TABLE002 as A
join TABLE001 as B on B.EMPLOYEE = A.EMPLOYEE and B.GENDER = 'F'

Is there a difference? This is just an example ... Both examples give the same results.

There is no difference for an `inner join`. But it would be a difference for a `left join` — juergen d, Jul 19 '20 at 06:35
Yes. I was asking for joins/inner joins. Thank you all for your time and answeres!!! Please be safe and take care of yourself and your family! — HEki, Jul 19 '20 at 12:49

score 2 · Answer 1 · answered Jul 19 '20 at 11:14

2

There is no difference per se, but alternatively you can use this code which would take less time in general to execute:

select * from Logins
where Employee in
(
     select Employee from Employees
     where Gender = 'F'
)

answered Jul 19 '20 at 11:14

iminiki

2,549
12
35
45

IN in a sql query always make the code look beautiful – Giriteja Bille Jul 19 '20 at 11:31
I agree. I prefer using `in` as much as I'm able to in my codes. – iminiki Jul 19 '20 at 11:33
I will for sure try this in a bigger sql querry!!! Thank you! – HEki Jul 19 '20 at 12:34

score 0 · Answer 2 · answered Jul 19 '20 at 10:19

If both your queries produce the result you want, then they are both valid solutions to your problem. In that way they are equal.

If you want to see if the database engine is internally handling both queries the same way, then you need to look at the underlying query plan (a.k.a. execution plan). If you are using Mirosoft SQL Server Management Studio to run your queries, then you can start learning about query plans here.

Specifically, you want to compare the actual execution plan of both queries. If both plans are equal, then the database engine is handling both queries the same way under the hood.

The correct way of joining two tables

2 Answers2