Which is the correct JOIN to use in this case?

Question

I have three tables named exam, student and subject.

You can learn more about the data by checking this link: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b80b08d0517678b8da3b70d489462d4f

I want to see the columns test, sub_code, marks from exam table and name column from subject table. I will use WHERE clause to get data of a particular roll number.

I came up with this query:

SELECT exam.test "Test", subject.name "Subject", exam.sub_code "Subject Code", exam.marks "Marks"
FROM exam
INNER JOIN subject ON exam.sub_code = subject.sub_code
WHERE roll_no=1
ORDER BY exam.test,exam.sub_code;

but I am not sure if this is the correct way to join the tables. I just followed help from people in one of the Stack Overflow chatrooms and online articles; so I am not sure if this is the correct JOIN to use.

I tried using this https://joins.spathon.com/ but I could not understand which JOIN is correct because of the huge number of records in exam table (and I am a noob in JOIN statements).

Should I use LEFT JOIN or JOIN or some other method?

Also, is it possible to format SQL output like this:

+------+-----------------------+--------------+-------+
| Test | Subject               | Subject Code | Marks |
+------+-----------------------+--------------+-------+
| MT1  | Maths                 | 041          |    29 |
|      | Physics               | 042          |    36 |
|      | Chemistry             | 043          |    42 |
|      | Informatics Practices | 065          |    46 |
|      | English               | 301          |    38 |
+------+-----------------------+--------------+-------+
| MT2  | Maths                 | 041          |    37 |
|      | Physics               | 042          |    43 |
|      | Chemistry             | 043          |    45 |
|      | Informatics Practices | 065          |    48 |
|      | English               | 301          |    40 |
+------+-----------------------+--------------+-------+
| MT3  | Maths                 | 041          |    42 |
|      | Physics               | 042          |    40 |
|      | Chemistry             | 043          |    37 |
|      | Informatics Practices | 065          |    45 |
|      | English               | 301          |    45 |
+------+-----------------------+--------------+-------+

Answer 1

Your INNER JOIN is suitable for the summarising type of queries - although an OUTER JOIN (in this case LEFT) can be useful, for example:

SELECT
  e.roll_no,  e.sub_code, e.marks, e.test,
  COALESCE(sb.name, '----') AS s_name, COALESCE(sb.sub_code, '----') AS s_code
FROM
  exam e
LEFT JOIN
  subject sb ON e.sub_code = sb.sub_code
WHERE sb.name IS NULL;

Result (---- = NULL):

roll_no sub_code    marks   test    s_name  s_code
      3      044       29    MT1    ----    ----
      5      044       39    MT1    ----    ----
     16      044       43    MT1    ----    ----
      3      044       33    MT2    ----    ----
      5      044       40    MT2    ----    ----
     16      044       38    MT2    ----    ----
      3      044       40    MT3    ----    ----
      5      044       42    MT3    ----    ----
     16      044       40    MT3    ----    ----

So, students 3, 5 & 16 have marks in an exam (subject code 044 - 3 tests each MT1, MT2 & MT3 - 9 grades in total) for a subject which apparently doesn't exist in the subject table. A check of the data reveals that ("040","Biology"); should probably be ("044","Biology"); - see the last snippet of this fiddle.

This part about the LEFT [OUTER] JOIN was put in following a comment by @RickJames about how I was too quick off the mark saying that because there were no NULLs in the data, OUTER JOINs didn't apply - this was incorrect in the general sense - for the aggregates that you want, it is correct, but an unfortunate over-generalisation on my part and clumsily put!

The OUTER keyword is useful when you want to see what "isn't there" - i.e. how many students didn't take any exams, or didn't take the MT3 ones? How many subjects had no exams?

For your summarising, the best you can do with MySQL is something like the SQL below (all of the code is available on the fiddle here). If you want nice formatting, then I suggest that you investigate ([Open Source] Business Intelligence tools - that is what they are for, not what an RDBMS is for)!

Note WITH ROLLUP!

SELECT 
  exam.test AS "Test", 
  subject.name AS "Subject", 
  exam.sub_code AS "Subject Code", 
  exam.marks AS "Marks", 
  MIN(exam.marks), MAX(exam.marks), AVG(exam.marks)
FROM exam
INNER JOIN subject ON exam.sub_code = subject.sub_code  -- INNER is optional - left in for clarity
WHERE roll_no=1
GROUP BY exam.test, subject.name, exam.sub_code, exam.marks WITH ROLLUP
ORDER BY 
  ISNULL(exam.test), exam.test ASC,  -- ASC is the default, but I like including it!
  ISNULL(subject.name), subject.name ASC,
  ISNULL(exam.sub_code), exam.sub_code ASC,
  ISNULL(exam.marks), exam.marks ASC;

Result: (Note ----- and -1' are placeholders for NULL`!)

Test    Subject Subject Code    Marks   MIN(exam.marks) MAX(exam.marks) AVG(exam.marks)
MT1 Chemistry      043    42      42    42    42.0000
MT1 Chemistry      043    -1      42    42    42.0000
MT1 Chemistry    ----     -1      42    42    42.0000
MT1 English        301    38      38    38    38.0000
MT1 English        301    -1      38    38    38.0000
MT1 English       ----    -1      38    38    38.0000
MT1 IP(*)          065    46      46    46    46.0000  -- (*) Informatics Practices
...
... snipped for brevity
...
MT1 Physics      ----     -1      36    36    36.0000
MT1 --------     ----     -1      29    46    38.2000  -- End of MT1 - overall AVG/MAX/MIN for MT1
MT2 Chemistry     043     45      45    45    45.0000
...
...
...
49 rows   -- Note - 49 rows!

See the MySQL documentation on WITH ROLLUP. PostgreSQL's functionality is a bit more sophisticated, but even so, I'd go for window functions (and follow links at bottom) in this case - IMHO, looks neater and you can easily pick out your values of interest by putting an outer query around this one below:

SELECT 
  e.test AS "Test", 
  s.name AS "Subject", 
  e.sub_code AS "Subject Code", 
  e.marks AS "Marks",
  ROUND(AVG(e.marks) OVER (PARTITION BY e.sub_code), 1) AS "Avg/subj",
  ROUND(AVG(e.marks) OVER (PARTITION BY e.test), 1)     AS "Avg/test",
  ROUND(AVG(e.marks) OVER (), 1)                        AS "Avg overall"
FROM exam e
INNER JOIN subject s 
  ON e.sub_code = s.sub_code
WHERE roll_no = 1
ORDER BY e.test, e.sub_code;

Result:

Test   Subject  Subject Code  Marks  Avg/subj   Avg/test    Avg overall
 MT1     Maths           041     29      36.0       38.2           40.9
 MT1   Physics           042     36      39.7       38.2           40.9
 MT1 Chemistry           043     42      41.3       38.2           40.9
 MT1    IP (*)           065     46      46.3       38.2           40.9
 MT1   English           301     38      41.0       38.2           40.9
 MT2     Maths           041     37      36.0       42.6           40.9
 ...
 ... snipped for brevity
 ...
 15 rows    -- Note 15 rows!

So, no need for nasty NULL placeholders (-----, -1), fewer lines of output (15 vs. 49 - 49 = 15 * 3 + 3 lines for the test averages + 1 summary line)

A couple of points:

• use aliases (FROM exam e) in your queries - it makes them more readable and less error-prone

• learn about window functions - they are extremely powerful and will repay any effort spent learning them many times over!

Following a comment by the OP, I did a fiddle with averages per test and overall, and per subject, per test and overall - either you get 3 rows or 15 - but 5 is not possible!

Answer 2

The type of JOIN you need will always depend ​on your data and your goals.

The most common types of JOINs are as follows:

1. INNER JOIN aka JOIN - Returns only rows that match on your join clause in both entities.

2. LEFT OUTER JOIN aka LEFT JOIN - Returns all rows from the entity specified on the left side of the LEFT JOIN keyword, and only matching rows from the entity on the right side. NULL is filled in for the fields of the entity from the right side for the rows that don't match the entity from the left.

3. RIGHT OUTER JOIN aka RIGHT JOIN - Essentially the same as the LEFT JOIN but the direction is flipped. So now the entity on the right side will return all of its rows and only matching rows from the left side are returned.

4. FULL OUTER JOIN aka FULL JOIN - Returns all rows from both entities on each side. Where the rows match, the data is returned for both entities, where they don't match, NULL is filled in for the values of the columns of the one side that doesn't match the other. *Note this doesn't functionally exist as a keyword in MySQL, but as a concept is good to be aware of and can be accomplished by using a UNION clause between a LEFT JOIN and RIGHT JOIN.

5. CROSS JOIN - Returns the cross product of both entities. So a row is returned for every combination of each row between both entities.

So in your case you're joining between exam and subject. Specifically in your example you chose an INNER JOIN. This will only return rows where the sub_code exists in both tables. That means if you have a row for an exam that doesn't have a sub_code defined yet or was deleted from the subject table, then that exam row won't be returned. But you may know that such a situation isn't possible in your system, and therefore an INNER JOIN is ok here.

Conversely, if you wanted to list all subjects and their correlating exams, regardless if a particular subject even has any exams yet then an INNER JOIN wouldn't work since any subject without a correlating exam (no sub_code for a row in the exam table) would not be returned. Instead you'd want to use an OUTER JOIN, and specifically with the order of how you wrote the tables in your query, you'd want a RIGHT OUTER JOIN to ensure all rows from the subject table are returned, and any matching rows from the exam table are returned.

You may find this article on SQL JOINs with visuals of each join type helpful as well.