How to perform joint query in navicat

Cartesian product

For multi-table queries, table names should be separated by commas. In this way, all the data in multiple tables can be found out. The way to find out is the Cartesian product method (complete combination)

Many of the data found out in this way are invalid data. You can add conditions to multi-table queries to find the desired information.

Inner joins

can filter out valid information through the associated attributes between tables when designing the database.

Explicit inner join

SELECT * FROM employee AS e1,enterprise AS e2 WHERE e1.enterpriseId-e2.id;

Implicit inner join

SELECT * FROM employee e1 INNER JOIN enterprise e2 ON e1.enterpriseId=e2.id;

There is no essential difference between implicit inner join and explicit inner join.

Related recommendations: "Navicat for mysql graphic tutorial"

Outer connection

Based on a table , find out all the data in this table, and then associate it with another table. If it is associated, its information will be displayed. If there is no association, it will not be displayed as null.

Left Outer Join

SELECT * FROM employee e1 LEFT JOIN enterprise e2 ON e1.enterpriseId=e2.id;

Right Outer Join

SELECT * FROM employee e1 RIGHT JOIN enterprise e2 ON e1.enterpriseId=e2.id;

##Alias ​​of the table

There are two reasons for using table aliases:

1. For convenience and simplicity.

2. If you perform a multi-table query from a duplicate table, you must use an alias.

There are two ways to use aliases:

1. Directly add a space after the name of the table, and then give it a new name.

SELECT * FROM employee e1,enterprise e2 WHERE e1.enterpriseId-e2.id;

2.as is no different from direct naming, but it seems that the more standardized sql statements will be easier to view.

SELECT * FROM employee AS e1,enterprise AS e2 WHERE e1.enterpriseId-e2.id;

The above is the detailed content of How to perform joint query in navicat. For more information, please follow other related articles on the PHP Chinese website!