Understand the query command that uses two tables in MySQL(or Postgresql)

mysql join

create table student(id integer, name varchar(100));
insert into student(id, name) values(0, "yingshaoxo");
insert into student(id, name) values(1, "god");

create table takes(id integer, course varchar(100));
insert into takes(id, course) values(0, "english");
insert into takes(id, course) values(0, "coding");
insert into takes(id, course) values(0, "business");
insert into takes(id, course) values(1, "english");
insert into takes(id, course) values(2, "web_design");

select * from student;
select * from takes;

select student.id, student.name, takes.course from (student join takes on student.id = takes.id);

Other join keywords that may not useful

SELECT whatever
FROM TableA a
INNER JOIN TableB b
ON a.column_name_1 = b.column_name_1
WHERE b.ok = 'yes'

SELECT 
order_table.id AS Id,
user_table.banned AS User_got_banned,
order_table.request_at AS Day,
order_table.status AS Order_status
FROM Users user_table
INNER JOIN Trips order_table
ON (user_table.users_id = order_table.client_id)
WHERE (user_table.banned = 'No')
ORDER BY order_table.request_at ASC

The INNER JOIN keyword selects records that have matching values in both tables.

Most of the time, we use INNER JOIN, and don’t use OR in the WHERE/ON condition, otherwise, you’ll get repeat rows.

---

---

The LEFT JOIN command returns all rows from the left table, and the matching rows from the right table. The result is NULL from the right side, if there is no match.

ON should be used to define the join condition.

WHERE should be used to filter the data.

​