[tamibor]

Весьма похоже что проблема в кавычках
`email`
и WHERE должен быть после JOIN

SELECT id, number, password, first_name, last_name, email, language, mod_status, registered_at
FROM customers JOIN newsletter_subscriptions ON newsletter_subscriptions.customer_id=customers.id
WHERE store_id=4 AND password!='NULL' AND email IS NOT NULL AND mod_status IN ('active', 'pending')