MYSQL: how to select data from two tables without join statement !!

Question

User-1123701243 posted

Hi all 
I need to join data from two tables but without using left join statement. 
Ex:

select t1.*,t2.* from t1,t2 where t1.id=t2.id;

The above statement shows only matched records from both tables. 
BUT I need to select ALL records from t1 and matched records from t2. 
For ex if t1 has 10 records and t2 matches t1 in 4 records only, then the result must show 10 records. 

I don't want to use (LEFT JOIN), because I noticed that it takes longer time than using (WHERE) clause. 
In oracle I could use: where t1.id(+)=t2.id 
Ex: 

select t1.*,t2.* from t1,t2 where t1.id(+)=t2.id; 

thanks.

Answer 1

User-192709268 posted

Hi

try this

// Query1 
string sql1 = @"select ID,Name from Person  ";
 
//Query2 
string sql2 = @"select FirstName, LastName from Person"; 

string sql = sql1 + sql2; 

// Create connection 
MySqlConnection conn = new MySqlConnection(connString); 
try 
{ 
// Open connection 
conn.Open(); 
// Create command 
MySqlCommand cmd = new MySqlCommand(sql, conn); 
cmd.ExecuteScalar(); 
}
catch (MySqlException ex)
{
    ShowMessage(ex.Message);
}
Finally
{
   conn.Close();
}

Answer 2

User-271186128 posted

Hi human2x,

As for this issue, I suppose we can achieve it using following steps.

1. Create a temporary table (include table 1 and table 2 columns).
2. Query the matched records from table 1 and table2, and insert them into the temporary table.
3. Query the not matched records from table 1, and insert it into the temporary table.
4. Query the temporary table. Get the required value.

Best Regards,
Dillion