'What does the equal sign mean in this SQL Join statement?

I'm new to sql and do not understand what this join statement is doing. Does this statement ON people.state_code=states.state_abbrev mean that people.state_code and states.state_abbrev are now one?

SELECT people.first_name, 
       people.state_code, states.division
FROM people
JOIN states ON people.state_code=states.state_abbrev;
mysqlsql


Solution 1:[1]

It will take the columns first_name and state_code from the table people and the column division from the table states and put them together in a join table where the entries in the state_code and state_abbrev columns match. The join table is produced only for display in response to this query; the underlying tables with the data entries are not amended.

Solution 2:[2]

In this case the '=' means equal (like values are equal) and is part of the join condition based on which data is retrieved by the select statement. You are 'linking' the two tables based on a condition so you can retrieve related data...

Relational data base - there are relations between tables and between data.

For example:

table_1
PERSON_ID FIRST_NAME LAST_NAME ADDRESS_ID
1        |John      |Doe      |2

table_2
ADRESS_ID STREET
1         | 5th Avenue
2         | 1st Street

SELECT FIRST_NAME, STREET 
FROM table_1 t1
JOIN table_2 t2 ON t1.ADDRESS_ID = t2.ADDRESS_ID;

will return

John, 1st Street

Solution 3:[3]

Does this statement ON people.state_code=states.state_abbrev mean that people.state_code and states.state_abbrev are now one?

Answer: NO. people.state_code and states.state_abbrev should be the same value on the respective tables.

Let me give you an example taken from https://www.mysqltutorial.org/mysql-join/

Suppose you have below tables:

CREATE TABLE members (
    member_id INT AUTO_INCREMENT,
    members_name VARCHAR(100),
    PRIMARY KEY (member_id)
);

CREATE TABLE committees (
    committee_id INT AUTO_INCREMENT,
    committees_name VARCHAR(100),
    PRIMARY KEY (committee_id)
);

Some data examples:

+-----------+--------+
| member_id | members_name |
+-----------+--------+
|         1 | John   |
|         2 | Jane   |
|         3 | Mary   |
|         4 | David  |
|         5 | Amelia |
+-----------+--------+


+--------------+--------+
| committee_id | committees_name |
+--------------+--------+
|            1 | John   |
|            2 | Mary   |
|            3 | Amelia |
|            4 | Joe    |
+--------------+--------+

To do the INNER JOIN we can use members_name and committees_name not the id because they are auto_increment and the data are not related.

So the query would be:

SELECT 
    m.member_id, 
    m.members_name  AS member, 
    c.committee_id, 
    c.committees_name  AS committee
FROM members m
INNER JOIN committees c ON c.name = m.name;

Giving below result:

+-----------+--------+--------------+-----------+
| member_id | member | committee_id | committee |
+-----------+--------+--------------+-----------+
|         1 | John   |            1 | John      |
|         3 | Mary   |            2 | Mary      |
|         5 | Amelia |            3 | Amelia    |
+-----------+--------+--------------+-----------+

Conclusion: The values of the columns are equaly the same

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Schnitte
Solution 2 Dragos P.
Solution 3

Related Questions