Left joins, i need an explanation about a code

Question

i am watching a tutorial. There is a code which i don't understand what is supposed to do.

$sql = 'SELECT p.*, 
      a.screen_name AS author_name, 
      c.name AS category_name
    FROM 
      posts p
      LEFT JOIN 
        admin_users a ON p.author_id = a.id
      LEFT JOIN 
        categories c ON p.category_id = c.id
    WHERE 
      p.id = ?';

I read about the left joins but i didn't understand them. Can somebody please explain me the code i shared. Thanks in advance!

It does the same that all left JOINs do – Strawberry Dec 09 '17 at 11:32 — Strawberry, Dec 09 '17 at 11:32

Jamin · Accepted Answer · 2017-12-09T12:07:49.443

Imagine you have two tables. One that stores the information about the programmers on your website, and the other table that keeps track of their online purchases.

PROGRAMMERS Table

+--------------------------------------------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Desire   |  32 | 123 fake s|  3000.00 |
|  2 | Jamin    |  25 | 234 fake s|  2500.00 |
|  3 | Jon      |  23 | 567 fake s|  2000.00 |
|  4 | Bob      |  30 | 789 fake s|  1500.00 |
|  5 | OtherGuy |  31 | 890 fake s|  1000.00 |
|  6 | DudeMan  |  32 | 901 fake s|   500.00 |
+--------------------------------------------+

PURCHASES Table

+---------------------------------------------+
| ORDER_ID    | PROG_ID |    DATE    | PRICE  | 
+-------------+---------+---------------------|
|  1          | 1       |  1-1-2017  | 100    |
|  2          | 2       |  1-2-2017  | 200    |
|  3          | 6       |  1-3-2017  | 300    |
+---------------------------------------------|

You decide you need to make a new table to consolidate this information to a table that contains certain columns you want.

For example, you figure it would be nice for shipping purposes to have a table that has the ID, the NAME, the PRICE, and the DATE columns.

Currently, the tables we have don't display all of that in a single table.

If we were to LEFT JOIN these tables, we would end up filling the desired columns with NULL values where there is no information to join.

SELECT ID, NAME, PRICE, DATE
    FROM PROGRAMMERS
    LEFT JOIN PURCHASES
    ON PROGRAMMERS.ID = PURCHASES.PROG_ID;

Notice that I'm selecting the columns I want from the starting table, then joining the right table even though there might be missing information.

RESULTING TABLE

+-------------------------------------+
| ID | NAME     | PRICE   |   DATE    |
+----+----------+-----------------+---+
|  1 | Desire   | 100     | 1-1-2017  |
|  2 | Jamin    | 200     | 1-2-2017  |
|  3 | Jon      | NULL    |   NULL    |
|  4 | Bob      | NULL    |   NULL    |
|  5 | OtherGuy | NULL    |   NULL    |
|  6 | DudeMan  | 300     | 1-3-2017  |
+-------------------------------------+

For a visual representation of the difference between SQL JOINs check out https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins .

Thanks mate. Now i understand it. I just have one more simple question. Can you tell me why do we SELECT p.* in the code i showed earlier? I understand that ".*" will match each word that starts with the letter "p". But i can't understand the purpouse of that. — ThereIsNothingImpossible, Dec 09 '17 at 12:10
@DesireOn Please see: https://stackoverflow.com/a/3639964/7813290 about using " * ". In this case it seems you are getting every column in p. — Jamin, Dec 09 '17 at 12:14

Left joins, i need an explanation about a code

1 Answers1