How could I rewrite a query without using joins

Question

I would like to know how this query would be written if no joins were used. I been trying to figure it out for cases where joins aren't viable or can't be used(aren't available).

SELECT
    *
FROM
(
    table1
INNER JOIN
    table2
ON
    table1.id = table2.id
)
INNER JOIN
    table3
ON
(
    table1.id2 = table3.id2
)
AND
(
    table1.id3 = table3.id3
)
WHERE
    table1.id = 1

The reason I can't use joins is because the application uses HQL as opposed to standard SQL and HQL makes joins incredibly difficult to do.

Answer 1

It is impossible to extract data from two different tables without joining them in one way or another. You are using a JOIN statement BUT you can achieve the same thing by placing it in the where clause for example:

SELECT * FROM table1, table2 WHERE table1.id = table2.id AND ...

Answer 2

You can do the selects separated and do the joins within the application.

Answer 3

SQL Without Joins...

SELECT * FROM table1
SELECT * FROM table2
SELECT * FROM table3

That isn't going to help you though.

The question is, what is your desired output, and what reasons do you have for not wanting a join?

If you want data from multiple tables in a relational database then you will be joining the data. Maybe this is something you need to do in the application as James Black suggested.

If you could shed some more light on the situation though we may be able to help further.

Answer 4

often a subquery may be easier than a join if only selecting 1 value

SELECT e.Name, e.HireDate, (select SUM(CheckAmount) from EmployeeCheck where EmployeeID = e.ID) 
FROM Employee e

Answer 5

You can do it with correlated subqueries if you want, but joins are better.

Answer 6

If joins are off limits, then stay away from SQL databases.

Answer 7

Hibernate doesn't prevent you from doing joins if you have an association mapping in your hibernate map file. See, for example: http://www.jumpingbean.co.za/blogs/mark/hibernate_hql_inner_join_on_clause