I think your last statement is the best way.  You can also try

    SELECT A.*    
    from A left join B on 
        A.x = B.y
        where B.y is null

I use queries in the format of your second example.  A join is usually more scalable than a correlated subquery.

I also use left joins with a &quot;where table2.id is null&quot; type criteria.

Certainly seems to be more efficient than the nested query option.

Joins are generally faster (in MySQL), but you should also consider your indexing scheme if you find that it&#39;s still moving slowly.  Generally, any field setup as a foreign key (using INNODB) will already have an index set.  If you&#39;re using MYISAM, make sure that any columns in the ON statement are indexed, and consider also adding any columns in the WHERE clause to the end of the index, to make it a covering index.  This allows the engine to have access to all the data needed in the index, removing the need to make a second round-trip back to the original data.  Keep in mind that this will impact the speed of inserts/updates/deletes, but can significantly increase the speed of the query.

This helped me a lot. `Joins` are always faster than Sub Queries to give results:

    SELECT tbl1.id FROM tbl1 t1
    LEFT OUTER JOIN tbl2 t2 ON t1.id = t2.id 
    WHERE t1.id&gt;=100 AND t2.id IS NULL ;

I want to select a blob col from one table, base64 encode it and insert it into another tables. Is there any way to do this without round tripping the data out of the DB and through my app?

base64 encode in MySQL

I traditionally deploy a set of web pages which allow for manual validation of core application functionality. One example is LoggerTest.aspx which generates and logs a test exception. I&#39;ve always chosen to raise a DivideByZeroException using an approach similar to the following code snippet: 

    try
    {
       int zero = 0;
       int result = 100 / zero;
    }
    catch (DivideByZeroException ex)
    {
       LogHelper.Error(&quot;TEST EXCEPTION&quot;, ex);
    }

The code works just fine but I feel like there must be a more elegant solution.  Is there a best way to raise an exception in C#?

What&#39;s the best way to raise an exception in C#?

I have 2 tables (A and B) with the same primary keys. I want to select all row that are in A and not in B. The following works:

    select * from A where not exists (select * from B where A.pk=B.pk);

however it seems quite bad (~2 sec on only 100k rows in A and 3-10k less in B)

Is there a better way to run this? Perhaps as a left join?

    select * from A left join B on A.x=B.y where B.y is null;

On my data this seems to run slightly faster (~10%) but what about in general?


Mysql select where not in table

I have 2 tables (A and B) with the same primary keys. I want to select all row that are in A and not in B. The following works:
<pre><code class="hljs language-csharp">select * from A where not exists (select * from B where A.pk=B.pk);
</code></pre>
however it seems quite bad (~2 sec on only 100k rows in A and 3-10k less in B)
Is there a better way to run this? Perhaps as a left join?
<pre><code class="hljs language-csharp">select * from A left join B on A.x=B.y where B.y is null;
</code></pre>
On my data this seems to run slightly faster (~10%) but what about in general?

I am setting up a very small MySQL database that stores, first name, last name, email and phone number and am struggling to find the &#39;perfect&#39; datatype for each field. I know there is no such thing as a perfect answer, but there must be some sort of common convention for commonly used fields such as these. For instance, I have determined that an unformatted US phone number is too big to be stored as an unsigned int, it must be at least a bigint.

Because I am sure other people would probably find this useful, I dont want to restrict my question to just the fields I mentioned above.

What datatypes are appropriate for common database fields? Fields like phone number, email and address?

Common MySQL fields and their appropriate data types

Is there an easy way to run a MySQL query from the Linux command line and output the results in [CSV][1] format?

Here&#39;s what I&#39;m doing now:

```lang-none
mysql -u uid -ppwd -D dbname &lt;&lt; EOQ | sed -e &#39;s/        /,/g&#39; | tee list.csv
select id, concat(&quot;\&quot;&quot;,name,&quot;\&quot;&quot;) as name
from students
EOQ
```

It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.

  [1]: http://en.wikipedia.org/wiki/Comma-separated_values




How can I output MySQL query results in CSV format?

**My requirement**

A table needs to maintain a *status* column.

This column represents one of 5 states.

&lt;br&gt;
**initial design**

I figured I can just make it an integer column and represent the states using a numeric value.

  - 0 = start
  - 1 = running
  - 2 = crashed
  - 3 = paused
  - 4 = stopped

Since I don&#39;t want my app to maintain the mapping from the integers to their string description, I plan to place those in a separate state description table (relying on a FK relation).

Then I discovered that MySQL has an ENUM type which matches my requirement exactly.
Other than a direct dependency on MySQL, are there any pitfalls with using the ENUM type?

MySQL ENUM type vs join tables

I have a query to the effect of

    SELECT t3.id, a,bunch,of,other,stuff FROM t1, t2, t3 
    WHERE (associate t1,t2, and t3 with each other) 
    GROUP BY t3.id 
    LIMIT 10,20

I want to know to many total rows this query would return without the LIMIT (so I can show pagination information).

Normally, I would use this query:

    SELECT COUNT(t3.id) FROM t1, t2, t3 
    WHERE (associate t1,t2, and t3 with each other) 
    GROUP BY t3.id

However the GROUP BY changes the meaning of the COUNT, and instead I get a set of rows representing the number of unique t3.id values in each group.

Is there a way to get a count for the total number of rows when I use a GROUP BY?  I&#39;d like to avoid having to execute the entire query and just counting the number of rows, since I only need a subset of the rows because the values are paginated.  I&#39;m using MySQL 5, but I think this pretty generic.

Getting the number of rows with a GROUP BY query

After reading it, this is *not* a duplicate of [Explicit vs Implicit SQL Joins][1].
The answer may be related (or even the same) but the **question** is different.

---

What is the difference and what should go in each?

If I understand the theory correctly, the query optimizer should be able to use both interchangeably.


  [1]: https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins

SQL JOIN - WHERE clause vs. ON clause

I need to do a LINQ2DataSet query that does a join on more than one field (as

    var result = from x in entity
    join y in entity2 
           on x.field1 = y.field1 
    and 
              x.field2 = y.field2

I have yet found a suitable solution (I can add the extra constraints to a where clause, but this is far from a suitable solution, or use [this][1] solution, but that assumes an equijoin).

Is it possible in LINQ to join on multiple fields in a single join?

**EDIT**

    var result = from x in entity
                 join y in entity2
                 on new { x.field1, x.field2 } equals new { y.field1, y.field2 }

is the solution I referenced as assuming an equijoin above.

Further **EDIT**

To answer criticism that my original example was an equijoin, I do acknowledge that, My current requirement is for an equijoin and I have already employed the solution I referenced above.

I am, however, trying to understand what possibilities and best practices I have / should employ with LINQ. I am going to need to do a Date range query join with a table ID soon, and was just pre-empting that issue, It looks like I shall have to add the date range in the where clause.

Thanks, as always, for all suggestions and comments given

  [1]: https://stackoverflow.com/questions/345427/linq-to-sql-join-multiple-columns-from-the-same-table

How to do joins in LINQ on multiple fields in single join

Ok, here is my dilemma I have a database set up with about 5 tables all with the exact same data structure. The data is separated in this manner for localization purposes and to split up a total of about 4.5 million records.

A majority of the time only one table is needed and all is well. However, sometimes data is needed from 2 or more of the tables and it needs to be sorted by a user defined column. This is where I am having problems.

data columns:

    id, band_name, song_name, album_name, genre

MySQL statment:

    SELECT * from us_music, de_music where `genre` = &#39;punk&#39;

MySQL spits out this error:

    #1052 - Column &#39;genre&#39; in where clause is ambiguous

Obviously, I am doing this wrong. Anyone care to shed some light on this for me?

MySQL - Selecting data from multiple tables all with same structure but different data

Scenario:

Let&#39;s say I have two tables, TableA and TableB.  TableB&#39;s primary key is a single column (BId), and is a foreign key column in TableA.

In my situation, I want to remove all rows in TableA that are linked with specific rows in TableB: Can I do that through joins? Delete all rows that are pulled in from the joins?

    DELETE FROM TableA 
    FROM
       TableA a
       INNER JOIN TableB b
          ON b.BId = a.BId
          AND [my filter condition]

Or am I forced to do this:

    DELETE FROM TableA
    WHERE
       BId IN (SELECT BId FROM TableB WHERE [my filter condition])

The reason I ask is it seems to me that the first option would be much more effecient when dealing with larger tables.

Thanks!

T-SQL: Selecting rows to delete via joins

I am wondering how to differentiate all these different joins ...

What is the difference between Left, Right, Outer and Inner Joins?

I have a MySQL query that joins two tables 

 - Voters
 - Households

They join on `voters.household_id` and `household.id`.

Now what I need to do is to modify it where the voter table is joined to a third table called elimination, along `voter.id` and `elimination.voter_id`. However the catch is that I want to exclude any records in the voter table that have a corresponding record in the elimination table.

How do I craft a query to do this?

This is my current query:

    SELECT `voter`.`ID`, `voter`.`Last_Name`, `voter`.`First_Name`,
           `voter`.`Middle_Name`, `voter`.`Age`, `voter`.`Sex`,
           `voter`.`Party`, `voter`.`Demo`, `voter`.`PV`,
           `household`.`Address`, `household`.`City`, `household`.`Zip`
    FROM (`voter`)
    JOIN `household` ON `voter`.`House_ID`=`household`.`id`
    WHERE `CT` = &#39;5&#39;
    AND `Precnum` = &#39;CTY3&#39;
    AND  `Last_Name`  LIKE &#39;%Cumbee%&#39;
    AND  `First_Name`  LIKE &#39;%John%&#39;
    ORDER BY `Last_Name` ASC
    LIMIT 30 



MySQL Join Where Not Exists

I think I&#39;m going down the right path with this one... Please bear with me as my SQL isn&#39;t the greatest

I&#39;m trying to query a database to select everything from one table where certain cells don&#39;t exist in another. That much doesn&#39;t make a lot of sense but I&#39;m hoping this piece of code will

    SELECT * from employees WHERE NOT EXISTS (SELECT name FROM eotm_dyn)

So basically I have one table with a list of employees and their details. Then another table with some other details, including their name. Where there name is not in the eotm_dyn table, meaning there is no entry for them, I would like to see exactly who they are, or in other words, see what exactly is missing.

The above query returns nothing, but I know there are 20ish names missing so I&#39;ve obviously not gotten it right.

Can anyone help?

SELECT * WHERE NOT EXISTS

How can I push into an array if neither values exist? Here is my array:

    [
        { name: &quot;tom&quot;, text: &quot;tasty&quot; },
        { name: &quot;tom&quot;, text: &quot;tasty&quot; },
        { name: &quot;tom&quot;, text: &quot;tasty&quot; },
        { name: &quot;tom&quot;, text: &quot;tasty&quot; },
        { name: &quot;tom&quot;, text: &quot;tasty&quot; }
    ]

If I tried to push again into the array with either `name: &quot;tom&quot;` or `text: &quot;tasty&quot;`, I don&#39;t want anything to happen... but if neither of those are there then I want it to `.push()`

How can I do this?


Array.push() if does not exist?

I want to write a query for MS SQL Server that adds a column into a table. But I don&#39;t want any error display, when I run/execute the following query.

I am using this sort of query to add a table ...
    
    IF EXISTS (
           SELECT *
           FROM   sys.objects
           WHERE  OBJECT_ID = OBJECT_ID(N&#39;[dbo].[Person]&#39;)
                  AND TYPE IN (N&#39;U&#39;)
       )

But I don&#39;t know how to write this query for a column.


Content Type	Original Author	Original Content on Stackoverflow
Question	BCS	View Question on Stackoverflow
Solution 1 - Mysql	Nick Berardi	View Answer on Stackoverflow
Solution 2 - Mysql	Bill Karwin	View Answer on Stackoverflow
Solution 3 - Mysql	Dave Rix	View Answer on Stackoverflow
Solution 4 - Mysql	ChoNuff	View Answer on Stackoverflow
Solution 5 - Mysql	user3136147	View Answer on Stackoverflow

Mysql select where not in table

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

Solution 5 - Mysql

What's the best way to raise an exception in C#?

base64 encode in MySQL

Attributions