Left Join VS. Multiple Select Statements

LEFT JOIN vs. multiple SELECT statements

There is not enough information to really answer the question. I've worked on applications where decreasing the query count for one reason and increasing the query count for another reason both gave performance improvements. In the same application!

For certain combinations of table size, database configuration and how often the foreign table would be queried, doing the two queries can be much faster than a LEFT JOIN. But experience and testing is the only thing that will tell you that. MySQL with moderately large tables seems to be susceptable to this, IME. Performing three queries on one table can often be much faster than one query JOINing the three. I've seen speedups of an order of magnitude.

JOIN queries vs multiple queries

This is way too vague to give you an answer relevant to your specific case. It depends on a lot of things. Jeff Atwood (founder of this site) actually wrote about this. For the most part, though, if you have the right indexes and you properly do your JOINs it is usually going to be faster to do 1 trip than several.

How to perform a LEFT JOIN in SQL Server between two SELECT statements?

SELECT * FROM 
(SELECT [UserID] FROM [User]) a
LEFT JOIN (SELECT [TailUser], [Weight] FROM [Edge] WHERE [HeadUser] = 5043) b
ON a.UserId = b.TailUser

SQL left join vs multiple tables on FROM line?

The old syntax, with just listing the tables, and using the WHERE clause to specify the join criteria, is being deprecated in most modern databases.

It's not just for show, the old syntax has the possibility of being ambiguous when you use both INNER and OUTER joins in the same query.

Let me give you an example.

Let's suppose you have 3 tables in your system:

Company
Department
Employee

Each table contain numerous rows, linked together. You got multiple companies, and each company can have multiple departments, and each department can have multiple employees.

Ok, so now you want to do the following:

List all the companies, and include all their departments, and all their employees. Note that some companies don't have any departments yet, but make sure you include them as well. Make sure you only retrieve departments that have employees, but always list all companies.

So you do this:

SELECT * -- for simplicity
FROM Company, Department, Employee
WHERE Company.ID *= Department.CompanyID
  AND Department.ID = Employee.DepartmentID

Note that the last one there is an inner join, in order to fulfill the criteria that you only want departments with people.

Ok, so what happens now. Well, the problem is, it depends on the database engine, the query optimizer, indexes, and table statistics. Let me explain.

If the query optimizer determines that the way to do this is to first take a company, then find the departments, and then do an inner join with employees, you're not going to get any companies that don't have departments.

The reason for this is that the WHERE clause determines which rows end up in the final result, not individual parts of the rows.

And in this case, due to the left join, the Department.ID column will be NULL, and thus when it comes to the INNER JOIN to Employee, there's no way to fulfill that constraint for the Employee row, and so it won't appear.

On the other hand, if the query optimizer decides to tackle the department-employee join first, and then do a left join with the companies, you will see them.

So the old syntax is ambiguous. There's no way to specify what you want, without dealing with query hints, and some databases have no way at all.

Enter the new syntax, with this you can choose.

For instance, if you want all companies, as the problem description stated, this is what you would write:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID

Here you specify that you want the department-employee join to be done as one join, and then left join the results of that with the companies.

Additionally, let's say you only want departments that contains the letter X in their name. Again, with old style joins, you risk losing the company as well, if it doesn't have any departments with an X in its name, but with the new syntax, you can do this:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID AND Department.Name LIKE '%X%'

This extra clause is used for the joining, but is not a filter for the entire row. So the row might appear with company information, but might have NULLs in all the department and employee columns for that row, because there is no department with an X in its name for that company. This is hard with the old syntax.

This is why, amongst other vendors, Microsoft has deprecated the old outer join syntax, but not the old inner join syntax, since SQL Server 2005 and upwards. The only way to talk to a database running on Microsoft SQL Server 2005 or 2008, using the old style outer join syntax, is to set that database in 8.0 compatibility mode (aka SQL Server 2000).

Additionally, the old way, by throwing a bunch of tables at the query optimizer, with a bunch of WHERE clauses, was akin to saying "here you are, do the best you can". With the new syntax, the query optimizer has less work to do in order to figure out what parts goes together.

So there you have it.

LEFT and INNER JOIN is the wave of the future.

NotORM's join vs multiple selects

I would expect the single query to be quite a bit quicker. However will depend on the communication between php and MySQL. When I have tried to benchmark it there has been a noticeable overhead just for doing a query (however simple).

However if the query gets too complicated you land up with something that is hard to maintain.

Another point comes if you are trying to use any custom procedures. For example I needed to do some processing based on Levenshtein distances (ie, how similar words are). The MySQL function I found for this was FAR slower than retrieving the data and processing with PHPs Levenshtein function.

MySQL - Joining multiple select statements

Simple inner join and a litle math should do the trick

CREATE TABLE Subcode
    (`Maincode` int, `Controlcode` int, `Subcode` int, `Description` varchar(12))
;
    
INSERT INTO Subcode
    (`Maincode`, `Controlcode`, `Subcode`, `Description`)
VALUES
    (01, 01, 123, 'Test Account'),
    (01, 02, 124, 'Test Account')
;


✓

✓

CREATE TABLE Voucher
    (`Voucherdate` Date, `Vouchertype` varchar(2), `Vouchernumber` int, `Maincode` int, `Controlcode` int, `Subcode` int, `Description` varchar(14), `Debit` int, `Credit` int)
;
    
INSERT INTO Voucher
    (`Voucherdate`, `Vouchertype`, `Vouchernumber`, `Maincode`, `Controlcode`, `Subcode`, `Description`, `Debit`, `Credit`)
VALUES
    ('2019-07-13', 'BV', 01, 01, 01, 123, 'Entering Test', 100, 0),
    ('2019-07-13', 'BV', 01, 01, 02, 124, 'Enterting Test', 0, 100)
;


✓

✓

SELECT * FROM Voucher;


Voucherdate | Vouchertype | Vouchernumber | Maincode | Controlcode | Subcode | Description    | Debit | Credit
:---------- | :---------- | ------------: | -------: | ----------: | ------: | :------------- | ----: | -----:
2019-07-13  | BV          |             1 |        1 |           1 |     123 | Entering Test  |   100 |      0
2019-07-13  | BV          |             1 |        1 |           2 |     124 | Enterting Test |     0 |    100

CREATE TABLE OpeningBalance
    (`Maincode` int, `Controlcode` int, `Subcode` int, `Debitbalance` int, `Creditbalance` int)
;
    
INSERT INTO OpeningBalance
    (`Maincode`, `Controlcode`, `Subcode`, `Debitbalance`, `Creditbalance`)
VALUES
    (01, 01, 123, 100, 0),
    (01, 02, 124, 100, 0)
;


✓

✓

SELECT 
s.Description
  ,o.Maincode
  ,o.Controlcode
  ,o.Subcode
  , o.`Debitbalance` Mainbalance
 , v.`Debit` + o.`Debitbalance` Totaldebit 
 , v.`Credit` + o.`Creditbalance` Totalcredit 
 , v.`Debit` + o.`Debitbalance` -( v.`Credit` + o.`Creditbalance`)  Openingbalance 
FROM OpeningBalance o inner Join Voucher v 
ON o.Maincode = v.Maincode AND o.Controlcode = v.Controlcode AND o.Subcode = v.Subcode
INNER JOIN Subcode s ON o.Maincode = s.Maincode AND o.Controlcode = s.Controlcode AND o.Subcode = s.Subcode
WHERE  MONTH(v.voucherdate) <= 7 AND YEAR(v.voucherdate) <= 2019


Description  | Maincode | Controlcode | Subcode | Mainbalance | Totaldebit | Totalcredit | Openingbalance
:----------- | -------: | ----------: | ------: | ----------: | ---------: | ----------: | -------------:
Test Account |        1 |           1 |     123 |         100 |        200 |           0 |            200
Test Account |        1 |           2 |     124 |         100 |        100 |         100 |              0

SELECT (SELECT sc.description AS description, 
           sc.maincode, 
           sc.controlcode, 
           sc.subcode, 
           ( ob.debitbalance - ob.creditbalance ) AS mainbalance 
    FROM   Subcode AS sc 
           LEFT JOIN OpeningBalance AS ob 
                  ON ( sc.maincode = ob.maincode 
                       AND sc.controlcode = ob.controlcode 
                       AND sc.subcode = ob.subcode ) 
    GROUP  BY sc.maincode, 
              sc.controlcode, 
              sc.subcode 
    ORDER  BY sc.maincode, 
              sc.controlcode, 
              sc.subcode ASC) AS test, 
   (SELECT Sum(v.debit)                               AS totaldebit, 
           Sum(v.credit)                              AS totalcredit, 
           ( mainbalance + totaldebit - totalcredit ) AS openingbalance 
    FROM   Subcode AS sc 
           LEFT JOIN Voucher AS v 
                  ON ( sc.maincode = v.maincode 
                       AND sc.controlcode = v.controlcode 
                       AND sc.subcode = v.subcode ) 
    WHERE  Substring(v.voucherdate, 1, 7) < '07-2019' 
    GROUP  BY sc.maincode, 
              sc.controlcode, 
              sc.subcode 
    ORDER  BY sc.maincode, 
              sc.controlcode, 
              sc.subcode ASC) AS test2


Operand should contain 1 column(s)

SELECT Substring(voucherdate, 1, 7) FROM Voucher


| Substring(voucherdate, 1, 7) |
| :--------------------------- |
| 2019-07                      |
| 2019-07                      |

db<>fiddle here

Multiple Table Select vs. JOIN (performance)

They are the same, but with a different syntax. So you shouldn't expect any performance difference between the two syntaxes. However the the last syntax(ANS SQL-92 syntax) is the recommended, see these for more details:

Bad habits to kick : using old-style JOINs.
SQL JOIN: is there a difference between USING, ON or WHERE?

Left Join VS. Multiple Select Statements