Unique Student id Count between Two table who borrowed at least one books from Library1 or Library 2-CodePudding

I need to count the total number of students who Borrowed at least 1 book from Library1 OR Library2.

Students table: 

id | name
---------
1     A
2     B
3     C
4     D
5     E

Library1 table:

id | student_id | book_id
-------------------------
1      1           2
2      1           3
3      2           3

Library2 table:

id | student_id | book_id
-------------------------
1      3           2
2      1           1
3      2           2

From the above tables Expected result Total = 3

Help me to solve this using Laravel DB query or MySQL.

CodePudding user response：

Use UNION (which removes duplicates) for the student_ids of both tables and aggregate:

SELECT COUNT(*)
FROM (
  SELECT student_id FROM Library1
  UNION
  SELECT student_id FROM Library2
) t

Or, UNION ALL (which returns duplicates) and count the distinct student_ids:

SELECT COUNT(DISTINCT student_id)
FROM (
  SELECT student_id FROM Library1
  UNION ALL
  SELECT student_id FROM Library2
) t