How can you merge two tables without losing any of the rows in SQL?

Question

I'm trying to merge two tables. A student table and a department table, but I don't want to lose any of the departments that don't have students. When I use NATURAL JOIN I lose them because the primary key is DEPT_ID and some of the departments don't have students so they don't show up in the merged table. I tried too look up other way to merge tables I haven't had any luck so far.

My Attempt:

SELECT DEPT_NAME, 
COUNT(DISTINCT STUDENT_ID) AS NumberOfStudentsInDepartment
FROM Students RIGHT JOIN Departments
GROUP BY DEPT_NAME
ORDER BY 2 DESC, 1 ASC;

Answer 1

Use Right Join

Select * from Students S RIGHT JOIN Department D on D.Dept_Id = S.Dept_id

Or Left Join

Select * from Department D LEFT JOIN Students S on D.Dept_Id = S.Dept_id

Learn more about how joins work here