Questions tagged [where-clause]
A syntactic form in functional languages used to bind a value to a name in a local scope. Similar, but not identical to, let.
9,114
questions
962
votes
22
answers
1.0m
views
SQL JOIN: what is the difference between WHERE clause and ON clause?
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.
(Note: this question is not a duplicate of ...
- 77k
587
votes
16
answers
553k
views
How to make "case-insensitive" query in Postgresql?
Is there any way to write case-insensitive queries in PostgreSQL, E.g. I want that following 3 queries return same result.
SELECT id FROM groups where name='administrator'
SELECT id FROM groups ...
- 21.7k
288
votes
18
answers
323k
views
What is the difference between HAVING and WHERE in SQL?
What is the difference between HAVING and WHERE in an SQL SELECT statement?
EDIT: I have marked Steven's answer as the correct one as it contained the key bit of information on the link:
When GROUP ...
- 6,805
285
votes
10
answers
220k
views
SQL - HAVING vs. WHERE
I have the following two tables:
1. Lecturers (LectID, Fname, Lname, degree).
2. Lecturers_Specialization (LectID, Expertise).
I want to find the lecturer with the most Specialization.
When I try ...
- 8,297
276
votes
7
answers
220k
views
WHERE vs HAVING
Why do you need to place columns you create yourself (for example select 1 as "number") after HAVING and not WHERE in MySQL?
And are there any downsides instead of doing WHERE 1 (writing the whole ...
- 7,705
248
votes
7
answers
628k
views
Left Join With Where Clause
I need to retrieve all default settings from the settings table but also grab the character setting if exists for x character.
But this query is only retrieving those settings where character is = 1,...
- 8,577
231
votes
7
answers
456k
views
Select records from NOW() -1 Day
Is there a way in a MySQL statement to order records (through a date stamp) by >= NOW() -1 so all records from the day before today to the future are selected?
- 2,391
221
votes
15
answers
337k
views
Detect if value is number in MySQL
Is there a way to detect if a value is a number in a MySQL query? Such as
SELECT *
FROM myTable
WHERE isANumber(col1) = true
- 7,319
221
votes
19
answers
1.0m
views
How to use NULL or empty string in SQL
I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.
- 3,197
175
votes
6
answers
114k
views
Does the order of where clauses matter in SQL?
Let's say I have a table called PEOPLE having three columns, ID, LastName, and FirstName. None of these columns are indexed.
LastName is more unique, and FirstName is less unique.
If I do two searches:...
- 3,654
164
votes
10
answers
238k
views
SQL : BETWEEN vs <= and >=
In SQL Server 2000 and 2005:
what is the difference between these two WHERE clauses?
which one I should use on which scenarios?
Query 1:
SELECT EventId, EventName
FROM EventMaster
WHERE EventDate ...
- 217k
163
votes
8
answers
339k
views
Laravel where on relationship object
I'm developing a web API with Laravel 5.0 but I'm not sure about a specific query I'm trying to build.
My classes are as follows:
class Event extends Model {
protected $table = 'events';
...
- 1,907
163
votes
3
answers
287k
views
MySQL join with where clause
I have two tables I want to join.
I want all of the categories in the categories table and also all of the categories subscribed to by a user in the category_subscriptions table.
essentially this is ...
- 3,935
160
votes
8
answers
81k
views
Linq: What is the difference between Select and Where
The Select and Where methods are available in Linq. What should every developer know about these two methods? For example: when to use one over the other, any advantages of using one over the other, ...
144
votes
5
answers
72k
views
Haskell: Where vs. Let
I am new to Haskell and I am very confused by Where vs. Let. They both seem to provide a similar purpose. I have read a few comparisons between Where vs. Let but I am having trouble discerning when to ...
user295190
131
votes
6
answers
100k
views
MySQL - ORDER BY values within IN()
I'm hoping to sort the items returned in the following query by the order they're entered into the IN() function.
INPUT:
SELECT id, name FROM mytable WHERE name IN ('B', 'A', 'D', 'E', 'C');
OUTPUT:...
- 3,856
129
votes
2
answers
43k
views
C# generic "where constraint" with "any generic type" definition?
Let me give example:
I have some generic class/interface definition:
interface IGenericCar< T > {...}
I have another class/interface that I want to relate with class above, for example:
...
- 25.7k
119
votes
6
answers
639k
views
How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?
Database: Sybase Advantage 11
On my quest to normalize data, I am trying to delete the results I get from this SELECT statement:
SELECT tableA.entitynum
FROM tableA q
INNER JOIN tableB u on (u....
- 1,331
117
votes
6
answers
309k
views
MySQL select with CONCAT condition
I'm trying to compile this in my mind.. i have a table with firstname and lastname fields
and i have a string like "Bob Jones" or "Bob Michael Jones" and several others.
the thing is, i have for ...
- 6,907
116
votes
16
answers
246k
views
Get only records created today in laravel
How do I use the created_at field to get only the records that were created today and no other day or time?
I was thinking of a ->where('created_at', '>=', Carbon::now()) But Im not sure that ...
- 12.7k
115
votes
3
answers
436k
views
querying WHERE condition to character length?
I have a database with a large number of words but i want to select only those records where the character length is equal to a given number (in example case 3):
$query = ("SELECT * FROM $db WHERE ...
- 3,036
115
votes
4
answers
140k
views
Filter Table Before Applying Left Join
I have 2 tables, I want to filter the 1 table before the 2 tables are joined together.
Customer Table:
╔══════════╦═══════╗
║ Customer ║ State ║
╠══════════╬═══════╣
║ A ║ S ║
...
- 1,985
109
votes
7
answers
182k
views
SQL server ignore case in a where expression
How do I construct a SQL query (MS SQL Server) where the "where" clause is case-insensitive?
SELECT * FROM myTable WHERE myField = 'sOmeVal'
I want the results to come back ignoring the case
- 5,988
108
votes
10
answers
299k
views
SQL WHERE condition is not equal to?
Is it possible to negate a where clause?
e.g.
DELETE * FROM table WHERE id != 2;
- 8,427
106
votes
6
answers
109k
views
"Or" equivalent in Linq Where() lambda expression
Is there a method in Linq where you can use to build SQL strings like "...where (a=1) OR (a=2)"?
- 8,628
95
votes
12
answers
550k
views
SELECTING with multiple WHERE conditions on same column
Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column...
My table is a very simple ...
- 1,053
93
votes
6
answers
231k
views
Conditional WHERE clause in SQL Server
I am creating a SQL query in which I need a conditional where clause.
It should be something like this:
SELECT
DateAppr,
TimeAppr,
TAT,
LaserLTR,
Permit,
LtrPrinter,
...
user2721874
93
votes
5
answers
125k
views
EF: Include with where clause [duplicate]
As the title suggest I am looking for a way to do a where clause in combination with an include.
Here is my situations:
I am responsible for the support of a large application full of code smells.
...
- 1,876
87
votes
4
answers
99k
views
In Haskell, when do we use in with let?
In the following code, the last phrase I can put an in in front. Will it change anything?
Another question: If I decide to put in in front of the last phrase, do I need to indent it?
I tried ...
- 5,831
86
votes
3
answers
158k
views
pandas equivalent of np.where
np.where has the semantics of a vectorized if/else (similar to Apache Spark's when/otherwise DataFrame method). I know that I can use np.where on pandas.Series, but pandas often defines its own API to ...
- 50.8k
73
votes
2
answers
168k
views
MySQL Select last 7 days
I read some posts here and seems like nothing special but I can not still select the entries of the last days.
SELECT
p1.kArtikel,
p1.cName,
p1.cKurzBeschreibung,
p1....
- 2,252
72
votes
2
answers
446k
views
Oracle SQL : timestamps in where clause
I need to look up rows within a particular time frame.
select *
from TableA
where startdate >= '12-01-2012 21:24:00'
and startdate <= '12-01-2012 21:25:33'
I.e.: I need to look up rows ...
- 905
72
votes
6
answers
285k
views
What is the purpose of using WHERE 1=1 in SQL statements? [duplicate]
Possible Duplicates:
Why would a sql query have “where 1 = 1”
Why would someone use WHERE 1=1 AND <conditions> in a SQL clause?
I've seen that a lot in different query ...
- 70.1k
72
votes
10
answers
81k
views
Is a JOIN faster than a WHERE?
Suppose I have two tables that are linked (one has a foreign key to the other):
CREATE TABLE Document (
Id INT PRIMARY KEY,
Name VARCHAR 255
)
CREATE TABLE DocumentStats (
Id INT PRIMARY KEY,
...
- 21.3k
69
votes
10
answers
153k
views
Dynamic WHERE clause in LINQ
What is the best way to assemble a dynamic WHERE clause to a LINQ statement?
I have several dozen checkboxes on a form and am passing them back as: Dictionary<string, List<string>> (...
- 25.1k
66
votes
6
answers
115k
views
How can I have IS NULL condition in TypeORM find options?
In my queries I'm using TypeORM find option.
How can I have IS NULL condition in the where clause?
- 1,106
65
votes
7
answers
81k
views
Checking an input param if not Null and using it in where in SQL Server
What is the best way to include an input param in the WHERE clause but exclude it if it is null?
There are a number of ways I believe, but I can't seem to remember then.
Also could I use the ...
- 24.1k
64
votes
6
answers
77k
views
In SQL / MySQL, what is the difference between "ON" and "WHERE" in a join statement?
The following statements give the same result (one is using on, and the other using where):
mysql> select * from gifts INNER JOIN sentGifts ON gifts.giftID = sentGifts.giftID;
mysql> select * ...
- 149k
63
votes
3
answers
32k
views
Usage of where in if let assignment in Swift
The Swift documentation at page 61 of the Swift manual hints to the possibility of using where to join an optional binding with a regular condition. Yet when I do it I have a warning suggesting me to ...
- 4,958
63
votes
4
answers
126k
views
sql query if parameter is null select all
Can the following query be modified to return all records if the ? is null?
SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ?;
- 2,605
60
votes
4
answers
117k
views
Selecting rows where remainder (modulo) is 1 after division by 2?
There is a column in options that hold an integer. I want to select the row only if that value % 2 = 1.
I know this can be done in 2 queries but is it possible to do it in 1?
- 691
58
votes
4
answers
239k
views
MySql Inner Join with WHERE clause [closed]
Here is my code:
SELECT table1.f_id FROM table1 WHERE table1.f_com_id = '430' AND
table1.f_status = 'Submitted'
INNER JOIN table2
ON table2.f_id = table1.f_id
where table2.f_type = '...
- 793
57
votes
5
answers
476k
views
Oracle date "Between" Query
I am using oracle database. I want to execute one query to check the data between two dates.
NAME START_DATE
------------- -------------
Small Widget 15-JAN-10 04.25.32....
- 8,174
54
votes
1
answer
87k
views
How to use numpy.where with logical operators
I'm trying to find the indices of all elements in an array that are greater than a but less than b. It's probably just a problem with my syntax but this doesn't work:
numpy.where((my_array > a) ...
- 3,414
53
votes
8
answers
65k
views
WHERE clause before INNER JOIN
If I have
SELECT * FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.id = t2.id
WHERE t1.user='bob';
Does the WHERE clause run after the two tables are JOINED?
How do I make it so it runs prior to the ...
- 805
50
votes
3
answers
39k
views
Can I use 'where' inside a for-loop in swift?
Is there also a possibility to use the 'where' keyword in another place then a switch? Can I use it in a for in loop for example?
I have an array with bools, all with a value, can I do something like ...
- 2,439
49
votes
13
answers
98k
views
why would you use WHERE 1=0 statement in SQL?
I saw a query run in a log file on an application. and it contained a query like:
SELECT ID FROM CUST_ATTR49 WHERE 1=0
what is the use of such a query that is bound to return nothing?
- 9,812
49
votes
3
answers
114k
views
SQL JOIN where to place the WHERE condition?
I have two following examples.
1. Example (WHERE)
SELECT 1
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE t2.field = true
2. Example (JOIN AND)
SELECT 1
FROM table1 t1
JOIN table2 ...
- 8,590
46
votes
5
answers
219k
views
SQL Not Like Statement not working
I have the following code within a stored procedure.
WHERE
WPP.ACCEPTED = 1 AND
WPI.EMAIL LIKE '%@MATH.UCLA.EDU%' AND
(WPP.SPEAKER = 0 OR
WPP.SPEAKER IS NULL) AND
WPP.COMMENT NOT ...
- 4,808
46
votes
12
answers
305k
views
DISTINCT clause with WHERE
How can I use the DISTINCT clause with WHERE? For example:
SELECT * FROM table WHERE DISTINCT email; -- email is a column name
I want to select all columns from a table with distinct email addresses....
- 1,224