Joining Tables in database (inner join)

Joining tables:

   Joins are a fundamental aspect of relation databases. They allow us to extract data on intites related by design represented by related tables in our data.

A join occurs when you retrieve data from tables related by some shared data, often a shared reference number or index. Foe example, if you have a table of customers as a table of orders, good design in a relation database will ensure that the two are related together by some common data between the tables, perhaps by storing a customer number on every order record . By performing a join, you can retrieve information in a result set where each row combines data from both tables: information about each order will be combined with information about the customer who placed it, and the combination will appear as such in the result set.

 INNER JOIN:

    IT IS THE MOST COMMON TYPE OF JOIN. WHEN YOU PERFORM AS INNER JOIN BETWEEN tables, rows with matching values are put into the result set, but where values do not match they are ignored. Because the joins looks for equivalence between the column it specifies and is considered the default type of join, it is sometimes called an equip-join or simply a join.

 We can wrore a join using INNER JOIN like this select * from table1 inner join table 2

On table1.d-column =table2.d-column;

 Or

Select *from table1,table2 wher table1’d_column= table2.’d-column.

Now we are going to perform an inner join between the tables to see who’s written which articles. We are going to relate the tables  as the user_id column, which exist in both tables.

Mysql> select u.name ,u auth –group a headline.

 From user as u

Inner join articles as n on

u.user _id =a.user_id;

in this example,the user –id column has the same name in both tables. So we can use the keyword using rather than on to simplify sql.

  Select u.name ,u auth_group, a headline  from usres as u

Inner join articles as a using (user_i.d):

Soppose we want to ensure that we only list people who have their auth_group set to writer:

Select u.name ,a headline from user as u

Inner join articles as a on u.user_id+ a.user_id where u.auth_group=’writer’.

Select u.name a headline from user asu ,articles as a where u.user _id+a.user_id

And u.auth_group=’writer’