Joins, your opinion

Anon

Hello...

In college, I always did my table joins like this: table1.key=table2.key

I used oracle in college, and now I am using MySQL. My boss doesn't really like doing it that way because it only joins when the keys are equal. He likes the LEFT JOIN and thinks thats more accurate or safer or something. He had never seen the way I did it. I never had a need to do the LEFT JOIN (I know the difference, it returns ) ... but he wants me to do it with the LEFT JOIN

Is it the SAME as matching the keys when I only want to return the records that match?

Thanks

Anon

The only benefit that I can see is that MySQL will optomize the statement with the LEFT JOIN syntax. Which could speed up your queries.

Personally I've always used table1.id = table2.is, but that could just be because I can never remember what LEFT, RIGHT and INNER mean. ;->

amcgrath

Im with you, Rob... 😉

Anon

They're for two different things! The two types of joins have different purposes. You use a regular join when you want to get ONLY those rows that have matching keys in each table. You use a left join if you want ALL rows in the left table matched with the right table, but if there are no matches, it returns NULL values. Sometimes you want all of these, sometimes you don't! It depends on your situation and what you're really after.

Anon

Yeah I understand that. But my boss wants ALWAYS to use Left Join.

Its just one of those things. If he doesn't know it or understand it.. he doesn't want to do it.

Thats why we do things the hard way around here.

Anon

or just don't tell him, and do it your way........

a few years ago my boss had an obsessions with image resolution and size... and couldn't get it through his head that for web use it didn't matter. about 4 of us teamed up on him and finally he gave in, but still insisted he was right.

Anon

Hardest thing in the world to do is educate your boss.

Anon

Its hard to do when every thing I write he has to analyize every line.. and come ask me why i did it that way like 3-4 months later... like i remember... then i have to go back through the code and see what the heck he is talking about.

amcgrath

Yea, I agree it might be a pain, but keeping a consistent programming style within an application is actually a pretty good thing.... 2cents worth....

Anon

I had a boss like that once. Thats when I started commenting my code liberally. You'd be surprised what a simple line like:

/ Select fields from the users table for manipulation /

can do to bring back your memory, and to keep you boss off of you back asking what you are doing.

Either that or sit down with him and ask him to review your code immediately when you submit it so that you won't be hip deep in another project and have to change gears to remember what you were doing 4 months ago, and then change back again to get back into your current project. Once your boss knows the productivity hit that you are taking to answer his questions I'll bet he'll start comming to you sooner, or stop comming to you all together.

rogue

Hi Nola,

Just show your boss the technical difference between using different types of JOINS. The results will be different!

(I'm using Oracle, so the syntax may be a touch different, sorry.)

SQL> CREATE TABLE DEPT
2 (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
3 DNAME VARCHAR2(14) ,
4 LOC VARCHAR2(13) ) ;

Table created.

SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2) );

Table created.

SQL> INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
SQL> INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
SQL> INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
SQL> INSERT INTO EMP VALUES
2 (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
SQL> INSERT INTO EMP VALUES
2 (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
SQL> INSERT INTO EMP VALUES
2 (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
SQL> INSERT INTO EMP VALUES
2 (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,40);
SQL> INSERT INTO EMP VALUES
2 (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,40);
SQL> INSERT INTO EMP VALUES
2 (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,40);

This is the way you do your queries (and I do mine, generally ;-):

SQL> select emp.empno, emp.ename, dept.dname
2 from emp, dept
3 where emp.deptno = dept.deptno;

 EMPNO ENAME      DNAME

7839 KING ACCOUNTING
7566 JONES RESEARCH
7369 SMITH RESEARCH

...which is the same as an INNER JOIN:

SQL> select emp.empno, emp.ename, dept.dname
2 from emp inner join dept
3 on emp.deptno = dept.deptno;

 EMPNO ENAME      DNAME

7839 KING ACCOUNTING
7566 JONES RESEARCH
7369 SMITH RESEARCH

What your boss is asking for is a LEFT JOIN, which says "if there are any rows in
the table on the right that don't match the rows on the left, MAKE UP a null row
so the table on the left will still get included in the query results" (notice there
are three more rows in the results, those members with deptno=40. There IS NO
deptno=40 in DEPT):

SQL> select emp.empno, emp.ename, dept.dname
2 from emp left outer join dept
3 on emp.deptno = dept.deptno;

 EMPNO ENAME      DNAME

7839 KING ACCOUNTING
7369 SMITH RESEARCH
7566 JONES RESEARCH
7654 MARTIN
7521 WARD
7499 ALLEN

6 rows selected.

A RIGHT JOIN is the same as the LEFT, except it makes up null rows for the table on
the left so rows on the right are guaranteed to be included in the query results.

SQL> select emp.empno, emp.ename, dept.dname
2 from emp right outer join dept
3 on emp.deptno = dept.deptno;

 EMPNO ENAME      DNAME

7839 KING ACCOUNTING
7566 JONES RESEARCH
7369 SMITH RESEARCH
SALES

4 rows selected.

One more is a FULL JOIN... which makes up rows in both tables, but this one is a
performance hog, as it basically does a UNION ALL on a RIGHT JOIN and a LEFT JOIN:

SQL> select emp.empno, emp.ename, dept.dname
2 from emp full outer join dept
3 on emp.deptno = dept.deptno;

 EMPNO ENAME      DNAME

7839 KING ACCOUNTING
7369 SMITH RESEARCH
7566 JONES RESEARCH
7654 MARTIN
7521 WARD
7499 ALLEN
SALES

7 rows selected.

Hope that helps!

smd

foxden

Yes

Anon

Thank you.. I'll print that out for future training sessions 🙂