• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

SQL: difference between using and not using joings

A

Alex

Diamond Member
this is my query (see attached code)

basically, i have one table that has references to 3 others so i select that data from my main table and from the others WHERE the ids are equal using AND for all 3 auxiliary tables

this could also be done using joins...

now im wondering whether there is any benefit in doing so?
it works perfectly as is...

thx 🙂
 
It does work fine that way and most likely is just as fast when executing. Joins just make a query easier to understand at first glance as they section off all of the relationships and reserve the WHERE condition for matching values only.
 
Originally posted by: KB
It does work fine that way and most likely is just as fast when executing. Joins just make a query easier to understand at first glance as they section off all of the relationships and reserve the WHERE condition for matching values only.
Click to expand...

good to hear that...

funny, joins just make it more complicated for me! 😛

as is i find it legible simple 🙂 but maybe thats just my warped mind.... 😛

thx!
 
Same result obtained, but I can guarantee you that using join statements, as well as properly indexed fields will speed up your queries quite a bit.

It takes a while to understand joins (been there, everyone has) but once you understand them, it's really not that complicated)

If you do not care about speed or page load, then at least do it for readability. Honestly, what you wrote in your post is really difficult to read.


 
Originally posted by: KB
It does work fine that way and most likely is just as fast when executing. Joins just make a query easier to understand at first glance as they section off all of the relationships and reserve the WHERE condition for matching values only.
Click to expand...

I would completely disagree with you on your first statement.

 
Originally posted by: Hyperblaze
Same result obtained, but I can guarantee you that using join statements, as well as properly indexed fields will speed up your queries quite a bit.

It takes a while to understand joins (been there, everyone has) but once you understand them, it's really not that complicated)

If you do not care about speed or page load, then at least do it for readability. Honestly, what you wrote in your post is really difficult to read.
Click to expand...

I found having a decent background in set theory really helped when I started to get into SQL. In fact, the last class I took on set theory focused heavily on SQL in the last few weeks.
 
Correct me if I'm wrong, but in SQL Server at least I believe there is no difference, since the query engine uses a join for all SELECT-FROM-WHERE statements anyway.
 
Originally posted by: Markbnj
Correct me if I'm wrong, but in SQL Server at least I believe there is no difference, since the query engine uses a join for all SELECT-FROM-WHERE statements anyway.
Click to expand...

Well, by doing it SELECT-FROM-WHERE you are leaving the some of the optimizations to the SQL Engine, which for the most part is okay. It's when you start doing complex queries with multiple joins that it can really burn you, at least in my experience with mysql.
 
just so i understand this better....the consensus is that using JOINS is faster?

im pretty decent at SQL but have always avoided joins like the plague... i guess its time to read up on that 🙂

EDIT: poll added to clear things up a bit!
 
You are using a join, Alex.

Its just that the syntax is different where you are not using the word "Join" in the statement but as far as the server is concerned, when it gets compiled, it will result in the exact same code. Look up SQL ANSI 92 standards. The thing is, the old style of doing joins (as you are doing it) might be discontinued in later versions of your SQL server software. You should really be using a "Join"...

They changed the standards, so technically you are using pre 1992 standards not using the word join.

--- You had

SELECT agendas.id, agendas.nome_agenda, agendas.email, agendas.endereco, agendas.id_cidade, agendas.id_estado, agendas.id_regiao, agendas.fone, agendas.cep, agendas.estado_civil, agendas.salario, agendas.bday, cidades.nome_cidade, estados.nome_estado, regioes.nome_regiao FROM agendas, estados, cidades, regioes WHERE agendas.id_cidade = cidades.id AND agendas.id_estado = estados.id AND agendas.id_regiao = regioes.id ORDER BY nome_agenda

Is the exact same as

SELECT agendas.id, agendas.nome_agenda, agendas.email, agendas.endereco, agendas.id_cidade, agendas.id_estado, agendas.id_regiao, agendas.fone, agendas.cep, agendas.estado_civil, agendas.salario, agendas.bday, cidades.nome_cidade, estados.nome_estado, regioes.nome_regiao FROM agendas
INNER JOIN estados ON agendas.id_estado = estados.id
INNER JOIN cidades ON agendas.id_cidade = cidades.id
INNER JOIN regioes ON agendas.id_regiao = regioes.id
ORDER BY nome_agenda

----

Now lets say you add in a subquery. Now with a join, it looks alot better:

SELECT agendas.id, agendas.nome_agenda, agendas.email, agendas.endereco, agendas.id_cidade, agendas.id_estado, agendas.id_regiao, agendas.fone, agendas.cep, agendas.estado_civil, agendas.salario, agendas.bday, cidades.nome_cidade, estados.nome_estado, regioes.nome_regiao, SubQuery1.something FROM agendas
INNER JOIN estados ON agendas.id_estado = estados.id
INNER JOIN cidades ON agendas.id_cidade = cidades.id
INNER JOIN regioes ON agendas.id_regiao = regioes.id
LEFT JOIN
(
SELECT agendas.id
agendas.something
FROM agendas
WHERE agendas.email = 'bill@microsoft.com'
) AS Subquery1 ON Subquery1.id = agendas.id
ORDER BY nome_agenda

 
Use joins. It may not be easier for you to read right now, but it is the way this is done, which means it is easier for everyone else to read. Furthermore, as someone else said, the optimization is easier for the db engine.
 
Originally posted by: brandonb
You are using a join, Alex.

Its just that the syntax is different where you are not using the word "Join" in the statement but as far as the server is concerned, when it gets compiled, it will result in the exact same code. Look up SQL ANSI 92 standards. The thing is, the old style of doing joins (as you are doing it) might be discontinued in later versions of your SQL server software. You should really be using a "Join"...

They changed the standards, so technically you are using pre 1992 standards not using the word join.

--- You had

SELECT agendas.id, agendas.nome_agenda, agendas.email, agendas.endereco, agendas.id_cidade, agendas.id_estado, agendas.id_regiao, agendas.fone, agendas.cep, agendas.estado_civil, agendas.salario, agendas.bday, cidades.nome_cidade, estados.nome_estado, regioes.nome_regiao FROM agendas, estados, cidades, regioes WHERE agendas.id_cidade = cidades.id AND agendas.id_estado = estados.id AND agendas.id_regiao = regioes.id ORDER BY nome_agenda

Is the exact same as

SELECT agendas.id, agendas.nome_agenda, agendas.email, agendas.endereco, agendas.id_cidade, agendas.id_estado, agendas.id_regiao, agendas.fone, agendas.cep, agendas.estado_civil, agendas.salario, agendas.bday, cidades.nome_cidade, estados.nome_estado, regioes.nome_regiao FROM agendas
INNER JOIN estados ON agendas.id_estado = estados.id
INNER JOIN cidades ON agendas.id_cidade = cidades.id
INNER JOIN regioes ON agendas.id_regiao = regioes.id
ORDER BY nome_agenda

----

Now lets say you add in a subquery. Now with a join, it looks alot better:

SELECT agendas.id, agendas.nome_agenda, agendas.email, agendas.endereco, agendas.id_cidade, agendas.id_estado, agendas.id_regiao, agendas.fone, agendas.cep, agendas.estado_civil, agendas.salario, agendas.bday, cidades.nome_cidade, estados.nome_estado, regioes.nome_regiao, SubQuery1.something FROM agendas
INNER JOIN estados ON agendas.id_estado = estados.id
INNER JOIN cidades ON agendas.id_cidade = cidades.id
INNER JOIN regioes ON agendas.id_regiao = regioes.id
LEFT JOIN
(
SELECT agendas.id
agendas.something
FROM agendas
WHERE agendas.email = 'bill@microsoft.com'
) AS Subquery1 ON Subquery1.id = agendas.id
ORDER BY nome_agenda
Click to expand...

thanks a lot brandon that was a very clarifying post! 🙂
i guess i gotta get up to date with my sql syntax... 😱
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top