Since Postgres came out with the ability to do LATERAL joins, I’ve been reading up on it, since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall query take four minutes or more. I understand that LATERAL joins may be able to help me, but even … Read more
In this excellent SO question, differences between CTE and sub-queries were discussed. I would like to specifically ask: In what circumstance is each of the following more efficient/faster? CTE Sub-Query Temporary Table Table Variable Traditionally, I’ve used lots of temp tables in developing stored procedures – as they seem more readable than lots of intertwined … Read more
I have a table with the following fields: id (Unique) url (Unique) title company site_id Now, I need to remove rows having same title, company and site_id. One way to do it will be using the following SQL along with a script (PHP): SELECT title, site_id, location, id, count( * ) FROM jobs GROUP BY … Read more
Using postgres 8.4, My goal is to update existing table: CREATE TABLE public.dummy ( address_id SERIAL, addr1 character(40), addr2 character(40), city character(25), state character(2), zip character(5), customer boolean, supplier boolean, partner boolean ) WITH ( OIDS=FALSE ); Initially i tested my query using insert statement: insert into address customer,supplier,partner SELECT case when cust.addr1 is not … Read more
I am running this query on MySQL SELECT ID FROM ( SELECT ID, msisdn FROM ( SELECT * FROM TT2 ) ); and it is giving this error: Every derived table must have its own alias. What’s causing this error? 4 Answers 4 Every derived table (AKA sub-query) must indeed have an alias. I.e. each … Read more
Why doesn’t the following work? SELECT name FROM (SELECT name FROM agentinformation) I guess my understanding of SQL is wrong, because I would have thought this would return the same thing as SELECT name FROM agentinformation Doesn’t the inner select statement create a result set which the outer SELECT statement then queries? 2 Answers 2
I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_id=category.id); I tried to delete them executing: DELETE FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM … Read more
I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don’t know why. I lack the theoretical knowledge to judge for myself if there is any difference. Is a sub-query as good as a JOIN and therefore is there nothing to worry … Read more
