SQL Query to Combine Two Tables Without a Common Column
In most of the queries in the learning process, we usually use to join those tables which have some common columns or relationships between them. But when it comes to real-life problems, then such easiest conditions are rarely found. Complex tasks like joining two tables without a common column is majorly found.
Let’s first have a look at our tables which we will be joining in this example:
Creating the Database:
Use the below SQL statement to create a database called geeks:
CREATE DATABASE GFG;
Using the Database:
Use the below SQL statement to switch the database context to geeks:
Now we create two tables named as table1 and table2
CREATE TABLE table1 ( Name1 VARCHAR(20), ID1 INT PRIMARY KEY, Salary1 INT ); CREATE TABLE table2 ( Name2 VARCHAR(20), ID2 INT PRIMARY KEY, Salary2 INT );
Now, adding data to tables
INSERT INTO table1 ValUES ('Harish',1,1000), ('Rakesh',2,2000), ('Mukesh',3,3000), ('Suresh',4,4000), ('Ramesh',5,4000); INSERT INTO table2 VALUES ('Astitva',1,4000), ('Maharaj',2,41000);
To verify the contents of the table use the below statement:
SELECT * FROM table1;
SELECT * FROM table2;
Now as we can see there are no two columns that are the same in the above two tables. Now to merge them into a single table we are having 3 different methods.
Method 1 (Cross Join): As you might have heard of several joins like inner join, outer join, in the same way cross join is there, which is used to form the Cartesian product of the tables without or with common columns. So since these joins doesn’t check for any column just they do a product of the two tables, so these joins are not regarded good for any query since repetition of data will be resulted from these joins. Also, inconsistency would be there. But since we are interested in knowing the methods so we are discussing it.
To do cross join we are just required to specify the name of table in FROM clause. No, WHERE clause is needed.
SELECT * FROM table1, table2;
Method 2 (UNION Method): This method is different from the above one as it is not merely a join. Its main aim is to combine the table through Row by Row method. It just adds the number of UNIQUE rows of the two tables and name the columns based on the first table specified in the method.
SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2;
This returns all the rows(unique) combined together under the column name of the TABLE1.
We can also query single columns or rename the columns in this method as shown below:
SELECT salary1 as salary FROM TABLE1 UNION SELECT salary2 as salary FROM TABLE2;
Hence we are able to select all the salaries possible and given to different clients in the two tables. This method returns unique data.
Method 3 (UNION ALL): The only difference between the UNION and UNIONALL method is that the previous one allows non-duplicates(unique) rows, but latter one results in all possible rows by combining the duplicates also.
SELECT salary1 as salary FROM TABLE1 UNION all SELECT salary2 as salary FROM TABLE2;
Hence we are able to see the three different methods to combine table with non-common columns. These methods are used under separate conditions as demanded.