What the real different between those two method to join tables in SQL

Question

I am seeing people using two different method in SQL to join the table; for example, I have two tables:

    Table A                                   Table B       
DeliveryDate    EMP     Hours          Date      EMP    Route
 5/14/2017     611300    1            5/14/2017 611300  11101
 5/14/2017     611301    3            5/14/2017 611301  11102
 5/13/2017     611300    4            5/13/2017 611300  11101
 5/13/2017     611301    5            5/13/2017 611301  11102
                                      5/13/2017 611301  11103

for those two tables, EMP(employee id) is string, hours is number, basically want to join tow table together in order to get hour many hours employee worked ans how many routes he worked. I saw people are using two different code to achieve the purpose, but what the real difference between those two methods? which one is better? code below:

Code 1:

SELECT C.DELIVERYDATE,C.EMP,C.HOURS,D.ROUTES
FROM
(
SELECT 
A.DeliveryDate, A.EMP, A.Hours
FROM 
A
) C,
(
SELECT
B.DATE,B.EMP,COUNT(B.ROUTE) AS ROUTES
FROM 
B
group BY B.DATE,B.EMP
)D
WHERE C.DeliveryDate=D.DATE
 AND    C.EMP=D.EMP

Code 2:

SELECT C.DELIVERYDATE,C.EMP,C.HOURS,D.ROUTES
FROM
(
SELECT 
A.DeliveryDate&A.EMP AS DATEEMP, A.DELIVERYDATE,A.EMP,A.Hours
FROM 
A
) C,
(
SELECT
B.DATE&B.EMP AS DATEEMP,B.DATE,B.EMP,COUNT(B.ROUTE) AS ROUTES
FROM 
B
group BY B.DATE,B.EMP
)D
WHERE C.DATEEMP=D.DATEEMP

Thanks so much

Possible duplicate of [Difference between these two joining table approaches?](https://stackoverflow.com/questions/5294311/difference-between-these-two-joining-table-approaches) — mtr.web, May 16 '18 at 20:01
I feel like neither of these codes are written efficiently - use proper join syntax — Daniel Marcus, May 16 '18 at 20:01
i don't think it is duplicate, any reason why they are not efficient? any better way? — Rowling, May 16 '18 at 20:07
You should use the modern syntax. And a bit of formatting goes a long way. This question is a bit odd though - in one you are including a date condition in the join, in the other you aren't. That's the difference and the right thing depends on what you're looking to do. — Error_2646, May 16 '18 at 20:16
@jarlh Are you aware of this? https://stackoverflow.blog/2018/04/26/stack-overflow-isnt-very-welcoming-its-time-for-that-to-change/ — Yunnosch, May 16 '18 at 20:18
@Yunnosch, yes, actually I've read it. Note that I did only comment one of the _"people are using two different code to achieve the purpose"_ alternatives... — jarlh, May 16 '18 at 20:23
@jarih Hi, the code was left years ago. I am new to SQL, so I know the he want to the count how many routes an employee have first in table 2, then join the hours information from table 1. but actually sometimes employee has hours but no route information in table 2, so it should be a left join. But the problem is you cannot join them first, since as you see, some people work on 2 routes on same day, but their total hours is 8, if you join 2 table directly, each route will have 8 hours — Rowling, May 16 '18 at 20:28
Reasons to avoid second alternative: 1) Write SQL easy to read! 2) The dbms will probably not be able optimize those DATEEMP join conditions, and the performance will suffer. — jarlh, May 17 '18 at 07:23

score 0 · Answer 1 · answered May 16 '18 at 20:09

0

Proper join syntex is like this:

Select * from table1 a join table2 b on a.fkey=b.pkey where [add conditions here]

answered May 16 '18 at 20:09

Daniel Marcus

2,686
1
7
13

Hi Daniel. Problem is there is PK and FK in those table, as you can see, I think I should join date with date, and employee id with employee ID at the same time. – Rowling May 16 '18 at 20:20
Yes I'm just showing you a sample of the syntax - in your case you would definitely want to join date with date, and employeeid with employeeid as you suggested – Daniel Marcus May 16 '18 at 20:21

score 0 · Answer 2 · answered May 16 '18 at 20:28

0

First, never use commas in the from clause. Always use proper, explicit, standard join syntax.

One proper way to do what you want is to aggregate before joining, such as:

SELECT DELIVERYDATE, EMP, C.HOURS, D.ROUTES
FROM (SELECT A.DeliveryDate, A.EMP, A.Hours
      FROM A
     ) A FULL JOIN
     (SELECT B.DATE, B.EMP, COUNT(B.ROUTE) AS ROUTES
      FROM B
      GROUP BY B.DATE, B.EMP
     ) D
     USING (DeliveryDate, Emp);

answered May 16 '18 at 20:28

Gordon Linoff

1,242,037
58
646
786

Thanks. I am new to SQL, you are right, I should count routes first before join, since some people worked on multiple routes, but will only have 1 record for hours; if I join first, I will have two routes share the same total hours, which is not right. In your query, if I want to do C left join on D, what should I change? never use the using function here you suggest. – Rowling May 16 '18 at 20:33
@Frank . . . A `FULL JOIN` keeps all dates/emp combinations that are in either table. An `INNER JOIN` keeps the combinations that are in both tables. A `LEFT JOIN` keeps the combinations in the first table. – Gordon Linoff May 17 '18 at 02:41

What the real different between those two method to join tables in SQL

2 Answers2