I have a issue with Order By in SQL

I having this issue when I try to retrieve data from two tables.

DEPT Table

EMP Table

I want to use this query

SELECT EMP.ENAME, DEPT.LOC, EMP.SAL
FROM EMP, DEPT
order by DEPT.DEPTNO;

but it’s creating the data with all locations in table DEPT

DATA

Answer

Basically your query is doing a CROSS JOIN which creates all possible combination of two tables. As DEPT table has 4 rows and EMP table has 11 rows then it will create total 44 rows as a final result.

Instead you should do this

SELECT 
    e.ENAME, 
    d.LOC, 
    e.SAL
FROM EMP e
JOIN DEPT d
on e.DEPTNO = d.DEPTNO
order by d.DEPTNO;