Getting started with SQL

Getting started with SQL

If you want to query two or more tables, you need to join the tables ).

0. flute Product

The simplest join method is to directly add two tables to the from clause and separate them with the join operator. The format is Table1 join Table2. The following is an example:

select e.fname, e.lname, d.namefrom employee e join department d;

108 rows of data are generated, because only join operations are performed on the Cartesian product. For two tables, m and n tuples are generated after Cartesian product. However, this is not the result we need. It contains a lot of unnecessary data. To obtain the correct result, you need to establish an internal connection.

1. Internal Connection

The internal connection is connected based on the same attribute value. You only need to add the on and all required attributes in the preceding table.

select e.fname, e.lname, d.namefrom employee e join department don e.dept_id = d.dept_id;

The result is exactly what you want. In fact, it is the default situation to add inner before join. But for good habits, it should be added to indicate what the connection is, which helps to read the code. On e. dept_id = d. dept_id can be replaced by using (dept_id.

The above results can also be operated using the SQL92 standard.

 

select e.fname, e.lname, d.namefrom employee e, department dwhere e.dept_id = d.dept_id;

 

The two standards have their own advantages and disadvantages.

2. Self-connection

Not only does a query contain the same table multiple times, but it can also be connected to the table itself. You only need to take different aliases.

Summary: The Connection operation method is easy to understand. For more than three connections, the connection subquery and unequal connections are similar.