SQLite query
-
I have the following three tables:
TABLE_A TABLE_B TABLE_C
id (primary) tablea_id (foreign) tablea_id (foreign)
name boards text
date
phoneI have the following query to select all rows from
TABLE_Aand also count how many related rows are in each of the other two tables:SELECT *, (SELECT COUNT(TABLE_B.tablea_id) FROM TABLE_B WHERE TABLE_B.tablea_id == TABLE_A.id) AS Cnt1, (SELECT COUNT(TABLE_C.tablea_id) FROM TABLE_C WHERE TABLE_C.tablea_id == TABLE_A.id) AS Cnt2 FROM TABLE_A
This works fine, but I was curious if: 1) SQL was doing three separate queries behind the scene, and 2) a more efficient way exists. Thank you. - DC
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles
-
I have the following three tables:
TABLE_A TABLE_B TABLE_C
id (primary) tablea_id (foreign) tablea_id (foreign)
name boards text
date
phoneI have the following query to select all rows from
TABLE_Aand also count how many related rows are in each of the other two tables:SELECT *, (SELECT COUNT(TABLE_B.tablea_id) FROM TABLE_B WHERE TABLE_B.tablea_id == TABLE_A.id) AS Cnt1, (SELECT COUNT(TABLE_C.tablea_id) FROM TABLE_C WHERE TABLE_C.tablea_id == TABLE_A.id) AS Cnt2 FROM TABLE_A
This works fine, but I was curious if: 1) SQL was doing three separate queries behind the scene, and 2) a more efficient way exists. Thank you. - DC
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles
If you turn on the Execution Plan SSMS will show you the 3 queries and their relative costs. I often use this tool to compare the performance of queries constructed differently to do the same job. I would use 3 union queries but I'd bet the cost is identical on such a simple query. Sorry I missed the SQL Lite platform in the title :-O
Never underestimate the power of human stupidity RAH
-
If you turn on the Execution Plan SSMS will show you the 3 queries and their relative costs. I often use this tool to compare the performance of queries constructed differently to do the same job. I would use 3 union queries but I'd bet the cost is identical on such a simple query. Sorry I missed the SQL Lite platform in the title :-O
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
Sorry I missed the SQL Lite platform in the title :O
That's okay. I could've qualified it further by stating it's running on an Android platform.
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles