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 ._.)
我有2张桌子。我想打印出table1中的所有访问列表,以及table2中的接口。但是一些访问列表没有与访问列表相关联的接口(但我仍然想要打印这些访问列表)。我该怎么做呢? (我无法得到理想的结果._。)
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
1 个解决方案
#1
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.
当您需要来自一个表的所有数据,并且只需要与另一个表匹配的数据时,通常可以采用OUTER JOIN。 LEFT JOIN实际上是LEFT OUTER JOIN的缩写,并指定哪个表(JOIN语句左侧的表)将返回所有数据。你总是可以使用RIGHT JOIN并反过来命名表(即table1 LEFT JOIN table2相当于table2 RIGHT JOIN table1),但LEFT JOIN语法更为常见。
A join that only returns matching data from both tables is known as an INNER JOIN, usually abbreviated to just JOIN.
仅返回来自两个表的匹配数据的连接称为INNER JOIN,通常缩写为JOIN。
#1
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.
当您需要来自一个表的所有数据,并且只需要与另一个表匹配的数据时,通常可以采用OUTER JOIN。 LEFT JOIN实际上是LEFT OUTER JOIN的缩写,并指定哪个表(JOIN语句左侧的表)将返回所有数据。你总是可以使用RIGHT JOIN并反过来命名表(即table1 LEFT JOIN table2相当于table2 RIGHT JOIN table1),但LEFT JOIN语法更为常见。
A join that only returns matching data from both tables is known as an INNER JOIN, usually abbreviated to just JOIN.
仅返回来自两个表的匹配数据的连接称为INNER JOIN,通常缩写为JOIN。