Sub-queries are the logically correct way to solve problems of the form, “Get facts from A, conditional on facts from B”. In such instances, it makes more logical sense to stick B in a sub-query than to do a join. It is also safer, in a practical sense, since you don’t have to be cautious … Read more
Do you want the resulting file on the server, or on the client? Server side If you want something easy to re-use or automate, you can use Postgresql’s built in COPY command. e.g. Copy (Select * From foo) To ‘/tmp/test.csv’ With CSV DELIMITER ‘,’ HEADER; This approach runs entirely on the remote server – it … Read more
INNER JOIN is ANSI syntax that you should use. It is generally considered more readable, especially when you join lots of tables. It can also be easily replaced with an OUTER JOIN whenever a need arises. The WHERE syntax is more relational model oriented. A result of two tables JOINed is a cartesian product of … Read more
Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well? See the article in my blog for detailed performance comparison: INNER JOIN vs. CROSS APPLY CROSS APPLY works better on things that have no simple JOIN condition. This one selects 3 … Read more
You can parameterize each value, so something like: string[] tags = new string[] { “ruby”, “rails”, “scruffy”, “rubyonrails” }; string cmdText = “SELECT * FROM Tags WHERE Name IN ({0})”; string[] paramNames = tags.Select( (s, i) => “@tag” + i.ToString() ).ToArray(); string inClause = string.Join(“, “, paramNames); using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) { … Read more
I believe an UPDATE FROM with a JOIN will help: MS SQL UPDATE Sales_Import SET Sales_Import.AccountNumber = RAN.AccountNumber FROM Sales_Import SI INNER JOIN RetrieveAccountNumber RAN ON SI.LeadID = RAN.LeadID; MySQL and MariaDB UPDATE Sales_Import SI, RetrieveAccountNumber RAN SET SI.AccountNumber = RAN.AccountNumber WHERE SI.LeadID = RAN.LeadID;
SELECT owner, table_name FROM dba_tables This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE … Read more
Single quotes are escaped by doubling them up, just as you’ve shown us in your example. The following SQL illustrates this functionality. I tested it on SQL Server 2008: DECLARE @my_table TABLE ( [value] VARCHAR(200) ) INSERT INTO @my_table VALUES (‘hi, my name”s tim.’) SELECT * FROM @my_table Results value ================== hi, my name’s tim.
You can use a subquery for this like select * from ( select * from emp order by sal desc ) where ROWNUM <= 5; Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information. Update: To limit the result with both lower and upper bounds things get … Read more
They are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.