MySQL - Select All Except What Is in This Table

Select all columns except one in MySQL?

Actually there is a way, you need to have permissions of course for doing this ...

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_omit>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>' AND TABLE_SCHEMA = '<database>'), ' FROM <table>');

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

Replacing <table>, <database> and <columns_to_omit>

Selecting all fields except only one field in mysql

you can do it easily like that

lets say your field is an id = 5

then

   select * from your_table where id !=5 

and if you mean columns

lets say you dont want select column3

then

   select column1,column2,column4 from tablename;

if you have many columns

    SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME),  '<columns_to_delete>,', '') 
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>'   AND TABLE_SCHEMA = '<database>'), ' FROM <table>');

    PREPARE stmt1 FROM @sql;
   EXECUTE stmt1;

MySQL - Select All Except what is in this Table

Using LEFT JOIN/IS NULL

---

   SELECT DISTINCT
          i.data
     FROM IMAGE i
     JOIN USER_IMAGE ui ON ui.image_id = i.id
LEFT JOIN USER u ON u.id = ui.user_id
                AND u.user = ?
    WHERE u.id IS NULL

Using NOT IN

---

SELECT DISTINCT
       i.data
  FROM IMAGE i
  JOIN USER_IMAGE ui ON ui.image_id = i.id
 WHERE ui.user_id NOT IN (SELECT u.id
                            FROM USER u
                           WHERE u.user = ?)

Using NOT EXISTS

---

SELECT DISTINCT
       i.data
  FROM IMAGE i
  JOIN USER_IMAGE ui ON ui.image_id = i.id
 WHERE NOT EXISTS(SELECT NULL
                    FROM USER u
                   WHERE u.id = ui.user_id
                     AND u.user = ?)

Performance:

---

The LEFT JOIN/IS NULL and NOT IN provide equivalent performance - the NOT EXISTS is ~30% less efficient. See this for more details.

Select all rows except one in MySQL

You have a few options:

SELECT * FROM table WHERE id != 4;

SELECT * FROM table WHERE NOT id = 4;

SELECT * FROM table WHERE id <> 4;

Also, considering perhaps sometime in the future you may want to add/remove id's to this list, perhaps another table listing id's which you don't want selectable would be a good idea.

In which case you would have:

SELECT * FROM table
WHERE id NOT IN (SELECT id FROM exempt_items_table);

select all row except those specified in the subquery

So here is answer:

SELECT t1.id 
FROM offers AS t1 
WHERE  t1.id NOT IN (
    SELECT f.id
    FROM (SELECT 
            ean,
            Min(net_price) AS minprice,
            stock
          FROM offers
          GROUP  BY ean
         ) 
    AS x INNER JOIN offers 
     AS f ON f.ean = x.ean AND f.net_price = x.minprice);

Select all except if specific cell value in MYSQL

That's a specific case when the type is 2.

To get the rows with type = 2 along with either (considering the expected output) journalUser = 1 or start_date = null, this can be written this way :

type = 2 AND (journalUser = 1 OR start_date IS NULL)

To make sure you have others type too, you can add an OR condition such as :

OR type <> 2.

This will give this query :

SELECT *
FROM journal
WHERE (type = 2 AND (journalUser = 1 OR start_date IS NULL))
    OR type <> 2

mysql query to select everything except

Using NOT IN:

SELECT w.*
  FROM WIDGET w
 WHERE w.widget_id NOT IN (SELECT c.widget
                             FROM CHOSEN c
                            WHERE c.user_id = $user_id)

Using NOT EXISTS:

SELECT w.*
  FROM WIDGET w
 WHERE NOT EXISTS (SELECT NULL
                     FROM CHOSEN c
                    WHERE c.widget_id = w.widget_id 
                      AND c.user_id = $user_id)

LEFT JOIN/IS NULL:

   SELECT w.*
     FROM WIDGET w
LEFT JOIN CHOSEN c ON c.widget_id = w.widget
                  AND c.user_id = $user_id
    WHERE w.widget IS NULL

Performance:

If the columns compared (widget_id in either table) are not nullable, LEFT JOIN/IS NULL performs the best on MySQL. If the columns are nullable (the value could be NULL), NOT IN or NOT EXISTS perform better.

---

Related Topics