SQL - inner join 2 tables but return all if 1 table empty

sql join left-join inner-join
viv_acious picture viv_acious · Mar 7, 2013 · Viewed 20.2k times · Source

I have 2 tables say A and B, and I want to do a join on them.

Table A will always have records in it.

When table B has rows in it, I want the query to turn all the rows in which table A and table B matches. (i.e. behave like inner join)

However, if table B is empty, I'd like to return everything from table A.

Is this possible to do in 1 query?

Thanks.

Answer

John Woo picture John Woo · Mar 7, 2013

Yes, for results like this, use LEFT JOIN.

Basically what INNER JOIN does is it only returns row where it has atleast one match on the other table. The LEFT JOIN, on the other hand, returns all records on the left hand side table whether it has not match on the other table.

To further gain more knowledge about joins, kindly visit the link below:

Related questions

SQL SELECT from multiple tables

How can I get all products from customers1 and customers2 include their customer names? customer1 table cid name1 1 john 2 joe customer2 table cid name2 p1 sandy p2 linda product table pid cid pname 1 1 phone 2 2 pencil 3 p1 pen 4 p2 paper Result …

Read More
Difference between left join and right join in SQL Server

I know about joins in SQL Server. For example. There are two tables Table1, Table2. Their table structures are the following. create table Table1 (id int, Name varchar (10)) create table Table2 (id int, Name varchar (10)) Table1 data as follows: Id …

Read More
Multiple left joins on multiple tables in one query

I've got one master table, which has items stored in multiple levels, parents and childs, and there is a second table which may or may not have additional data. I need to query two levels from my master table and …

Read More