Advantage Of Using The Join Operator

[tinoymalayil 0]

When i work with the sql, i have found the following two queries with and without JOIN Operator returns the same output.Can anybody help which method is more suitable?

Query with JOIN Operator

SELECT CM.CRSEID,CM.CRSENAME, CM.CRSELOGINID,CM.FILEPATH,CM.FILEURL,CM.FILEDESCFROM CRSETYPE CT JOIN CRSE CMON CT.CRSEID,=CM.CRSEID,WHERE CT.TYPE=1

Query without JOIN Operator

SELECT CM.CRSEID,CM.CRSENAME, CM.CRSELOGINID,CM.FILEPATH,CM.FILEURL,CM.FILEDESCFROM CRSETYPE CT,CRSE CMWHERE CT.TYPE=1 AND CT.CT.CRSEID,=CM.CRSEID

Please Reply

Regards,
Tinoy

[Ananya 0]

Well I have left working with these some time back

But it isn't a question of which query is better. As the JOIN operator does the job of both selecting rows from 2 tables and selecting those rows on the basis of equal values of the common attribute among 2 tables. As in your case you have 2 tables CRSETYPE and CRSE. They have a common attribute called CRSEID. JOIN operator will automatically retrieve rows from 2 tables having equal values for CRSEID.

See From clause does the job of cross product of data of 2 tables. JOIN eliminates those rows that can't be equated on the basis of common attribute value.

So in your 1st query ON CT.CRSEID,=CM.CRSEID, can be eliminated and can be rewritten as

 

SELECT CM.CRSEID,CM.CRSENAME, CM.CRSELOGINID,CM.FILEPATH,CM.FILEURL,CM.FILEDESCFROM CRSETYPE CT JOIN CRSE CMWHERE CT.TYPE=1

Join will equate CRSEID values of 2 tables.
Edited July 4, 2012 by moderator (see edit history)

[Quatrux 4]

When working with SQL, the standard SQL syntax is to use INNER JOIN and etc. syntax, but for example in ORACLE you usually just use commas to separate database tables and in the WHERE clause just write the conditions on which you join the tables, for LEFT or RIGHT JOINS in ORACLE you just use the (+) sign on the join conditions.I've tested that on ORACLE writing inner join can be slower sometimes, as I think the optimizer converts to ORACLE syntax, at least on ORACLE 10g.For me, it's much easier to read SQL queries using this syntax: select * from table1, table2, table3 where conditions;MySQL also supports this syntax and usually you have no problems with it, but as I know it's not in the standard, as SQL queries stand for Structured Query Language, it's like natural sentences, so as sentences it seems more logical to write inner join on conditions, but not otherwise, but for me, as I am used to different syntax, it's much harder to read those kind of SQL queries, especially if they are big and have a lot of conditions, sub-queries and etc.

[rpgsearcherz 5]

While the two do the same thing, the only thing I'm seeing here is that one is "easier to use," basically? Are there any differences in terms of the efficiency SQL-wise? Or does the system itself do the same thing regardless?

[Quatrux 4]

I've read that the new better way which separate the join conditions and other conditions is the inner join/left join syntax which is the new standard, but personally for me, I usually use the old method as it's much easier for me to read it, maybe because I am used to it? I work with large databases and big SQL queries which can be quite complex and when I look at the SQL query, I can put everything in my head how it works and what it does and searching for bugs or something is much easier for me, when I see what is selected, from what kind of tables and what are the conditions, but for that to work, the conditions needs to be put quite logically depending on those tables.

I've also worked with a guy which used different syntax, and every time I needed to look at his queries when he was away, it was quite hard for me to understand everything as there were lots of inner joins, lots of left joins and to see which tables are used I would need time to put everything into my head and especially it starts to be hard to read those kind of queries when you start using CASE or IF in the conditions, it isn't as clear for me as the old multiple tables syntax.

But I think, if you get used to one syntax, it might be different, but it's just my opinion, also I am used to my SQL beautifier which I configurated for myself and it really is useful.

Which is more efficient? At least on ORACLE 10g and 9i some queries started to work faster than I used the mutliple tables seperated with commas, on MySQL I guess the optimizer works differently.

For example, this SQL query:

SELECT * from table1 WHERE table1.id IN (1,2,3);

By optimizer is always changed too:

SELECT * from table1 WHERE table1.id = 1 OR table1.id = 2 OR table1.id = 3;

So theoretically the second method is faster, because the first method is always transformed to the second method, but the first method is much shorter and easier to read, and the speed you get isn't superb faster.

[Ananya 0]

In my opinion the 1 st query is optimized one as it will consume lesser CPU execution time. If table A has 3 rows and table B has 4 rows. As one puts 2 tables in From clause the cross product of 2 will give 3* 4=12 entries and from those entries based on equal common attribute value second selection will take place. This is what the second query is doing consuming a lot of CPU time.The golden rule of an optimized query is as:- Perform projection first and then selection so that the number of rows on which selection has to made gets reduced considerably.

[tinoymalayil 0]

Thank You Ananya and Quatrux for your valuable answers..

[Ananya 0]

Hi, tinoymalayil

 

In my opinion for having a good concept in SQL queries there are many books but the book on PL/SQL by

Ivan Bayross is good enough to reffer to.