Removing duplicate
If i have to remove duplicate from my output can i use DISTINCT keyword with my SELECT statement in place of group by?
Code:
SELECT DISTINCT
m.dept_no, m.emp_no, d.dept_name
FROM
employees.dept_manager_dup m
JOIN
employees.departments_dup d ON m.dept_no = d.dept_no
ORDER BY dept_no;
its giving preety same result. Can you tell me the difference. And one more thing if we have to use group by we can't just use m.emp_no, we have to use everything we have selected with SELECT statement.
Code:
SELECT
m.dept_no, m.emp_no, d.dept_name
FROM
employees.dept_manager_dup m
JOIN
employees.departments_dup d ON m.dept_no = d.dept_no
GROUP BY m.dept_no , m.emp_no , d.dept_name
ORDER BY dept_no;
Hi Bidhan!
Thanks for reaching out.
Using DISTINCT:
The DISTINCT keyword removes duplicate rows from your result set.
In your first query, SELECT DISTINCT ensures that each combination of dept_no, emp_no, and dept_name appears only once, even if it occurs multiple times in the joined table.
Using GROUP BY:
GROUP BY is typically used when you're applying aggregate functions (like SUM, AVG, etc.), grouping rows with common values in specified columns.
In your second query, GROUP BY m.dept_no, m.emp_no, d.dept_name groups rows by these three columns but doesn't aggregate any data. It functions similarly to DISTINCT in this case since there are no aggregate functions.
Key Difference:
DISTINCT removes duplicates, whereas GROUP BY is for aggregating data. When no aggregates are used, they can give similar results, but their purposes are different.
Note on GROUP BY:
You're correct that when using GROUP BY, you must include all selected columns (that aren't part of an aggregate function) in the GROUP BY clause. In your case, since you're not using aggregates, you need to group by all three selected columns.
Hope this helps.
Best,
Tsvetelin