How to combine data from two tables with no common fields in SQL?

Question

I have 2 tables: one table with many rows and a second table with one row. The tables have no fields in common. Is it possible to combine them into one table with many rows?

I've checked UNION, but MSDN says:

The following are basic rules for combining the result sets of two queries by using UNION:

• Each SELECT statement within UNION must have the same number of columns.
• The columns must also have similar data types.
• The columns in each SELECT statement must also be in the same order.

Example

This is what my tables look like right now:

      Table 1                            Table 2

Column1    Column2        Column4    Column5    Column3
-------    -------        -------    -------    -------
A          1                 E          10         a
B          2                                    
C          3                                    
D          4                                    

And this is what I'm trying to achieve as a result:

            CONSOLIDATED_Table 3                   
Column1    Column2  Column3    Column4    Column5
-------    -------  -------    -------    -------
A          1         E          10         a
B          2         E          10         a
C          3         E          10         a
D          4         E          10         a

Answer 1

You can add additional columns like this:

select tid, t_name, null as s_name
from teachers t
union all
select sid, null, s_name
from students s;

Answer 2

yes definitely you can use INNER JOIN . its a easy way to get data from multiple table. either you can use sub query and get data in Row format

Answer 3

We use joins to combine columns of multiple tables, whereas we use Union to join rows of multiple tables given that the columns types and nber of columns are the same in all the select queries with union. Since you want to show all the rows, we can use Union. Select tid as id, t_name as name from teachers union all Select sid as id, s_name as name from students;

Answer 4

Teachers and students

select * from (
    select 'teacher' as rowtype, tid as id , t_name as name
    from teachers
    union all
    select 'student', sid, s_name
    from students) t
order by name;

Answer 5

This approach produces a cartesian product, but since you have only 1 row in table 2, it should work for your specific use case.

select * from table_1, table_2;