Select from multiple tables without using join and union

Question

I have the following SQL query:

SELECT t1.id as id,
       t1.username as username,
       CONCAT_WS(' ', t2.ad,t2.soyad) as fullname,
       t2.title as title,
       t3.pass as password,
       t3.phone_login as hat,
       t2.time as date
FROM kullanici t1, kullanici_tanim t2, dial_users t3
WHERE t1.id = t2.usr_id AND t1.agent_id = t3.user_id
GROUP  BY  t1.id
ORDER BY t1.id ASC

This query works fine. What I'm wondering is should I use joins? What is the correct way?

Use `JOIN`s; you technically already are with the conditions in your `WHERE` clause, but you are doing so in an archaic and painful to read manner. — Uueerdo, Jun 30 '16 at 16:39
If works fine what is the problem .... just a tip... Promote the use of explict `JOIN` sintaxis, Aaron Bertrand wrote a nice article [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it. — Juan Carlos Oropeza, Jun 30 '16 at 16:39
even both will give same result as of now you are using Cross Join with where condition where as in Join condition you will filter by required columns — mohan111, Jun 30 '16 at 16:40
The modern JOIN syntax separates the link-criteria from the filter-criteria. Which makes it harder to forget a link-criteria and end up with a nasty cartesian product join. — LukStorms, Oct 31 '21 at 16:28
Comma means ("implicit") cross join with lower precedence than ("explicit") keyword joins. — philipxy, Oct 31 '21 at 21:16
Does this answer your question? [Explicit vs implicit SQL joins](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) — philipxy, Oct 31 '21 at 21:18

mechanical_meat · Answer 1 · 2022-03-01T15:51:56.340

0

For those who will have to use your code, please use joins. E.g.:

SELECT   t1.id as id,
             t1.username as username,
             CONCAT_WS(' ', t2.ad,t2.soyad) as fullname,
             t2.title as title,
             t3.pass as password,
             t3.phone_login as hat,
             t2.time as date
FROM kullanici t1 
     INNER JOIN kullanici_tanim t2 
     ON t1.id = t2.usr_id 
     INNER JOIN dial_users t3
     ON t1.agent_id = t3.user_id
GROUP  BY  t1.id
ORDER BY t1.id ASC

edited Mar 01 '22 at 15:51

answered Jun 30 '16 at 16:39

mechanical_meat

163,903
24
228
223

when I delete "GROUP BY t1.id", i get every row twice. how can i get one for every id without group by? – Invictus26 Jun 30 '16 at 16:55
Why do you want to not use GROUP BY? – mechanical_meat Jun 30 '16 at 18:05
I want to learn if it is possible to get only one result per row without group by? – Invictus26 Jun 30 '16 at 18:20
Try `SELECT DISTINCT ...` – mechanical_meat Jun 30 '16 at 18:27
Comma is also "ANSI-92-compliant code". – philipxy Mar 01 '22 at 00:09

Select from multiple tables without using join and union

1 Answers1