Hot answers tagged

5

It looks to me like it’s doing a Scan because it may well need rows from T553 if the condition in T1011 holds. On the other hand, if any of the conditions on T553 hold, it’ll need rows from T1011. So indexes would have to be able to handle finding rows in T553 and then pulling in the relevant rows from T1011, and also finding rows in T1011 and pulling in the ...


2

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's reasonably fast. It is valid (= guaranteed to always produce correct results) to push a selection (aka filter, predicate) below an inner join when the selection ...


2

As an alternative to coalescing column values one may want to use a default row on the non matching rows Using tsql dialect based on the comment: I've added a tag to indicate I would accept a Postgres specific answer. Still, standard SQL would be preferred if possible. Test data SELECT * INTO #a FROM (VALUES (1) , (2) , (3)) AS x(a1); ...


2

You can use only columns from the joined tables or in your case concatenated version of it SELECT first_name, last_name, address_1_zip, CONCAT_WS(',', last_name,first_name) AS CombinedName FROM CPSQL.dbo.legal_entity AS LE INNER JOIN CPSQL.dbo.cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style WHERE C.create_date >= Convert(...


1

Try to use explicit join. It seems that MySQL has some issue referencing the implicit cross join. select b.id, u.id, rating from books as b join users as u on 1=1 left join ratings on u.id = userid and b.id = bookid


1

Use set operations. If the user is 42, and the group is 1001, that would be (SELECT user_id_a FROM userfriend WHERE user_id_b = 42 UNION SELECT user_id_b FROM userfriend WHERE user_id_a = 42 ) EXCEPT SELECT user_id FROM usergroup WHERE group_id = 1001; UNION creates a result set that is the union of two query results and removes duplicate rows. ...


1

Like most (all?) SQL queries the number of columns in the result is fixed. Recursive CTEs can only add rows and not columns. You are fortunate that the result you want to generate a ARF report is a XML. Because of this you can use good old GROUP_CONCAT to generate an XML form of the DKIM. I haven't looked up the XML spec for the report however something in ...


1

Move the text columns to a separate table. This new table will have the same key as the original table. When a new row arrives write the small columns to the original table and the text columns to the new table with the same key value. This technique is called a vertical partition. If required there can be multiple new tables, each containing one or more of ...


1

Maybe. That is, there is no setting to achieve such; however, rewriting the query can achieve such. Please show us the query that is causing such. Meanwhile, here are some possible examples: SELECT * ... loads all the text columns. If you don't need all the columns, spell out what you need instead of saying *. SELECT big_text FROM t WHERE ... ORDER BY .....


1

A classical case of relational-division! One of the most efficient queries techniques for what you ask: SELECT h.name, h.vacancies FROM hotels_amenities_lookup ha1 JOIN hotels_amenities_lookup ha2 USING (hotel_id) -- more? JOIN hotels h ON h.id = ha1.hotel_id WHERE ha1.amenity_id = (SELECT id FROM amenities WHERE key = 'wheelchair_accessible')...


Only top voted, non community-wiki answers of a minimum length are eligible