Modifying sql query,dont want to use join operation

Question

The following query uses join operation, i want the same query to execute without using join operation. Is it possible to do so? If yes than how can i do it?

select jname, jcode from heardt inner join judge ON heardt.jud1 = jcode

The reason i am trying to do this is because i am using odbc connection for mysql and join operations are not getting executed at all as web page is loading for infinitely long and not giving any output. That is the reason why i want to try without using join operation

Are you able to post the C# code with the command and connection so we can better help you? — Tom 'Blue' Piddock, Nov 18 '10 at 12:57

score 2 · Accepted Answer · answered Nov 18 '10 at 08:07

2

I don't know your rationale, I find JOINS much easier to read but you can replace them by joining (no pun intented) the tables in the where clause.

select  jname
        , jcode 
from    heardt 
        , judge 
where   heardt.jud1 = judge.jcode

answered Nov 18 '10 at 08:07

Lieven Keersmaekers

57,207
13
112
146

i am using odbc connection for mysql and join operations are not getting executed as web page is loading for infinitely long and not giving any output. That is the reason why i want to try without using join operation – Ishan Nov 18 '10 at 08:13
@Ishan, I don't know much about ODBC connections but it seems very unlikely to me that the JOIN operations through the ODBC connection are the culprit for your performance problem. – Lieven Keersmaekers Nov 18 '10 at 08:36
2

This syntax invokes a join within SQL, it doesn't remove join from the logic. – Tom 'Blue' Piddock Nov 18 '10 at 09:22
@Blue, I assumed OP was talking about removing the JOIN *operator*, not *operation*. – Lieven Keersmaekers Nov 18 '10 at 09:40
@Lieven, he does mention operation in the question several times. – Tom 'Blue' Piddock Nov 18 '10 at 10:29
@Blue, true but the fact OP marked the answer as accepted, I think it was a valid assumption. What people write and what people mean are often two different things. – Lieven Keersmaekers Nov 18 '10 at 10:55
@Lieven I'm fully aware that what people write and mean are often different. However, considering what you wrote is generated in the SQL Engine to be exactly the same as what OP has written to be code that is not working. The code you wrote is syntactically generated the same as the "broken code" written by OP. So I still stand by that - the assumption, however valid in other questions, is wrong in this situation. – Tom 'Blue' Piddock Nov 18 '10 at 12:56

score 1 · Answer 2 · answered Nov 18 '10 at 08:40

There is no additional filter on that query. It might cause the query to return many rows. This could cause a slowdown, depending on the number of records in your table.

You should consider limiting the number of returned records from the query.

Something else you need to check, if there is an index on the JCode field

score 0 · Answer 3 · answered Nov 18 '10 at 08:09

0

Select jname, jud1 from heardt where not jud1 is null

EDIT: Ok, this was quick. So: Why do you need the 'join'?

answered Nov 18 '10 at 08:09

Rudi

3,124
26
35

1

I assume jname is a column from the judge table. If it itsn't, there was no reason to join the tables to begin with. OP might want to clarify this. – Lieven Keersmaekers Nov 18 '10 at 08:11

score 0 · Answer 4 · answered Nov 18 '10 at 08:27

The query Select jname, jud1 from heardt where not jud1 is null shows that jud1 has a value, but not that that value is valid. The join or where validates the relationship between the tables.

If your query takes a very long time to execute with the join in place it is most likely that you do not have correct indexes on the tables causing a table scan to take place instead of and indexed search.

score 0 · Answer 5 · edited May 23 '17 at 12:04

i am using odbc connection for mysql and join operations are not getting executed as web page is loading for infinitely long and not giving any output. That is the reason why i want to try without using join operation

That's probably not because your JOIN is not getting executed, but because your JOIN query is taking too long. And that's probably because you don't have the correct index defined (an index, preferably a clustered index, on judge.jcode).

If the join is still taking too long after adding such an index you could consider precaching the query with a table or indexed view (latter however not supported in MySQL).

score 0 · Answer 6 · answered Nov 18 '10 at 09:25

If you are able to run it SQL Manager you should be able to run it on the ODBC Connection, if not there is something wrong with the way you are instantiating that connection in C#.

Can you post the c# code you are using so we can make a better judged answer for you.

score 0 · Answer 7 · answered Nov 18 '10 at 09:36

As lieven pointed out I think his solution is a good one

select  jname
        , jcode 
from    heardt 
        , judge 
where   heardt.jud1 = judge.jcode

But you should create indexes in the fields you are joining, therefore the result will be provided much quickly so add

Create index a1 on heardt(jud1); Create index a2 on judge(jcode);

I think this is the better possible option

Modifying sql query,dont want to use join operation

7 Answers7