MySQL > select * from multiple tables

Previously we learned about single-table queries, but more often we use multi-table queries, such as two tables

Table (user ID, name, company ID)

Company Form (Company ID, Company name)

• Inner join

Select * from table where company id = 1; Inner can be omitted
select
	u.uname,
	u.company 'the company id',
	c.cname 'Company Name'
from
	cattle_user u
	inner join company c on ( u.company = c.cid );
Copy the code

---

• Left link -left join

-- The number of records in the left table, that is, the number of records in the left table, is displayed. The number of records in the right table that does not have corresponding data is left blank
select
	u.uname,
	u.company 'the company id',
	c.cname 'Company Name' 
from
	cattle_user u
	left join company c on ( u.company = c.cid );
Copy the code

---

• Right link – right join

-- How many records does the right table have, that is, how many records are displayed? If the left table has no corresponding data, the left table is left blank
select
	u.uname,
	u.company 'the company id',
	c.cname 'Company Name' 
from
	cattle_user u
	right join company c on ( u.company = c.cid );
Copy the code

---

• The subquery

-- Search for companies with a higher market cap than Alibaba
select * from company where market_value > (select market_value from company where cname = Alibaba);
Copy the code