-1

I am trying to join multiple tables.

Example(department_id is PK in department table and employee_id is PK in employee Table):

SELECT department.department_name, employee.employee_name
FROM employee
INNER JOIN department
  ON employee.department_id = department.department_id
WHERE employee.first_name = 'John';

From the above example, how does inner join work? on each entry in employee where first_name matches as 'John' will it join with department table?

Lets say we know before hand that, we have 100 names John and 99 belong to department 1 and 1 in department 2. As we know 99% of the department table ,

is it better to query department table seperately based on department table twice

OR

Is join more efficient here even if it tries to join and get the same department information for 99% of the rows?

Appreciate the response in advance. Thanks.

Phil
  • 157,677
  • 23
  • 242
  • 245
ging
  • 219
  • 7
  • 20
  • StackOverflow really isn't the place for opinion based answers, but generally, a single query will have better performance than multiple queries. – James Jun 05 '23 at 00:51
  • @James yeah i agree but in general i know inner join return all common elements. But what i am trying to understand from this post is, on each row of employee table will it try to search the department_name or lets say if it finds the department from the first row join , will it use it for subsequent rows to get the name if department is same or will it do again a join on each row? – ging Jun 05 '23 at 00:55
  • The code you provided will search ALL rows of both tables, and find all records with the first_name column that matches 'John'. There's no subsequent row logic here. – James Jun 05 '23 at 00:59
  • SQL is a "set-based" language that works with whole sets of data at a time. This means SQL is designed/optimised to work with entire tables or subsets of tables, rather than individual rows or columns. e.g. see [this previous answer](https://stackoverflow.com/questions/24168/why-are-relational-set-based-queries-better-than-cursors) – Paul Maxwell Jun 05 '23 at 01:32

1 Answers1

0

I Hope you mean to say INNER JOIN Vs Sub Query. We have a very detailed discussion on the same topic, kindly refer to that Previous Discussion

gskarthikkn
  • 89
  • 4