您的位置: 首页  >  技术问答  >  MySQL查询从多个表中选择，显示来自table1 + table2中的一些数据

MySQL查询从多个表中选择，显示来自table1 + table2中的一些数据

分类: 技术问答 • 2022-03-11 21:37:20
问题描述：

I have 2 tables. I want to print out all access-lists from table1, plus the interface from table2. But some access-lists don't have an associated interface with the access-list (but I still want to print these access-lists). How do i do this? (I just can't get the desired result result ._.)

table1

| id | access-list  | ... 
+----+--------------+ 
| 0  | list_1       | ...
| 1  | list_2       | ...
| 2  | list_3       | ...
| 3  | list_4       | ...

table2

| id | access-list  | interface |
+----+--------------+-----------+
| 0  | list_1       | iface0    |
| 1  | list_4       | iface1    |

The expected result:

0 list_1 iface0 bla bla bla 
1 list_2        bla bla bla
2 list_3        bla bla bla 
3 list_4 iface1 bla bla bla

我有2个表。 我想打印 table1 strong>中的所有访问列表，以及table2中的接口 strong>。 但是一些访问列表没有与访问列表相关联的接口（但我仍然想要打印这些访问列表）。 我该怎么做呢？ （我无法获得所需的结果._。） p>

table1 p> 

  |  id | 访问列表|  ... 
 + ---- + -------------- + 
 |  0 |  list_1 |  ... \ N |  1 |  list_2 |  ... \ N |  2 |  list_3 |  ... \ N |  3 |  list_4 |  ... 
 代码>  PRE> 
 
 
表2  P> 
 
 


  |  id | 访问列表| 界面| 
 + ---- + -------------- + ----------- + 
 |  0 |  list_1 |  iface0 | 
 |  1 |  list_4 |  iface1 | 
  code>  pre> 
 
 
预期结果： p> 
 
 
  0 list_1 iface0 bla bla bla 
1 list_2 bla bla bla \  n2 list_3 bla bla bla 
3 list_4 iface1 bla bla bla 
  code>  pre> 
  div>

答 

SELECT *
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.access_list = t2.access_list

When you need all the data from one table, and only the data that matches from another table, an OUTER JOIN is usually the way to go. LEFT JOIN is actually short for LEFT OUTER JOIN, and specifies which table (the one on the left of the JOIN statement) will have all the data returned. You can always use a RIGHT JOIN and name the tables the other way round (i.e. table1 LEFT JOIN table2 is equivalent to table2 RIGHT JOIN table1), but the LEFT JOIN syntax is much more common.

A join that only returns matching data from both tables is known as an INNER JOIN, usually abbreviated to just JOIN.

相关推荐