Hi guys
I have a little mess up in my head going on i dont really know why.
My question is how come a right join AND a left join were made to handle database queries, why both of them, isn t one enough?
for instance let s take the left join.
syntax:

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

and we get all the rows from table_name1 and rows from the other table which do have a match
Now if we SWITCH the tables
syntax:

 SELECT column_name(s)
    FROM table_name2
    LEFT JOIN table_name1
    ON table_name1.column_name=table_name2.column_name

and we get all the rows from table_name2 which we could have done with RIGHT JOIN as well but we would have to switch tables.

Once again WHY isn t a for example Left join enough why does a right join exist? VICE VERSA.

yes you are correct that in the scenario you described, you can just simply swap table1 and 2 in your statement and you can get away with just using the right or the left. however, in the bigger picture, you have to consider that you will first set up your database design, relationships and then data will be populated. You may be flexible in what type of data gets entered, allowing null values and you may need to create reports that include these null values so the left and right joins would be necessary where you dont have records that match. You may want to include all of the rows in table1 even if there isnt a match in table2 and vice versa. You arent going to be modifying these queries after the program is developed.

If its just running manual queries in the DB interface, then yes you can get comfortable with a left or right join and you control how to include the table names in your query.

Look at it this way:

select a.column1, b.column2, c.column3 
from tablea a 
left join tableb b
on a.id = b.a_id 
right join tablec c 
on a.id = c.a_id
and b.id = c.b_id

Keep adding tables and changing directions. If we only had left or right it wouldn't be possible.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.