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

Question

I am trying to join two tables. A table studentand a table department, but I do not want to lose any of the departments that have no students. When I use NATURAL JOIN, I lose them because the primary key DEPT_ID, and some of the departments do not have students, so they do not appear in the combined table. I also tried to find another way to merge tables. I'm not lucky yet.

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 the right link

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 connections work here.