Select from multiple tables

Hi guys,

I have two tables (tbl1,tbl2)

I was trying to run a single select query to select all the record from tbl1 and tbl2 but I got an empty result.

Here's my query:

SELECT a.*,b.* FROM tbl1 as a,tbl2 as b;

I found that the problem is tbl1 has no record but tbl2 has a record inside.

If tbl1 has no record, is there any way that I can still select all from tbl1 and tbl2 to get all the record from tbl2 instead of an empty result.

Thanks for any help.

Nau.

 

No join? Try to use outer join. Or if these two tables have same structure, use union.

 

As mentioned, you can use JOIN. Another way is using a union of two queries

select * from tbl1
union
select * from tbl2

Code (markup):

But this will work only if the two tables have the same columns. You can still use this even if the columns are different, but it's a bit tricky and not always efficient...
Post some more info if you haven't figured it yet

 

Thanks guys, I change my table structure and it works fine.

Cheers

 

You should change your codes.
select * from tbl1
union
select * form tbl2

 

I found this at



depending on what database platform you are using, the correct syntax probably looks something like:

SELECT *
FROM TableA AS a
FULL JOIN TableB AS b ON a.TableKey = b.TableKey

If for some reason you have no shared key value between the two tables you could do this:

SELECT *
FROM TableA AS a
FULL JOIN TableB AS b ON 1 = 1 --or a=a or any comparison that will always guarantee a match

If you want to join the two tables VERTICALLY rather than HORIZONTALLY (add rows to the data set, not columns), then you could use the UNION syntax suggested previously:

SELECT * FROM TableA
UNION ALL SELECT * FROM TableB

 

You can also use LEFT JOIN and then check for IS NULL / IS NOT NULL for a second talbe:

SELECT A.*, B.TableKey IS NULL AS is_table_B
FROM A
FULL JOIN B ON A.TableKey = B.TableKey


Купить землю в Киевской области Купить участок в Киевской области Купить землю под Киевом Купить участок под Киевом

 

I prefer this method over joins and unions:
SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1;

 

we have two mathod..one is using "join" and second one is "union"...

 

SELECT *
FROM tbl1 t1
LEFT JOIN tbl2 t2
ON t1.col = t2.col
UNION
SELECT *
FROM tbl1 t1
RIGHT JOIN tbl2 t2
ON t1.col>= t2.<col